Search, Find, and Fill in Offset

I've worked 5 hours on this and cannot get it to work; would appreciate
knowing what is wrong. If the user makes a change to any cell AU10:AU30 and
the entry is > 0, then I need to see if the offset (0,-2) to that entry in
Col AU is anywhere in Column L. If it is, then the macro should place the
target value just made in Col AU as an offset (0,3) to what is found in Col L
(there could be no instances found, 1, or many). Here is what I have, which
doesn't work; no error messages, it just doesn't work:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngToSearch As Range
Dim rngToFind As Range
Dim rngFound As Range
Dim rng As Range
Dim wks As Worksheet
Set wks = ActiveSheet

Application.EnableEvents = True
With wks
Set rngToSearch = .Columns("L")
Set rngToFind = Target.Offset(0, -2)
End With
If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
If Target.Value > 0 Then
For Each rng In rngToSearch
Set rngFound = rngToSearch.Find(What:=Target.Offset(0, -2), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then rng.Offset(0, 3).Value = Target.Value
Next rng
End If
End If
End Sub

Good Morning Paige,

I reviewed your code and made a few changes. When using the find method on
a range you need to use a For each Loop. The Find method will search the
complete range. So I made a few changes to your code. It has not been test,
but doing what your looking for. Good in your VBA Code..


Rick, Fairbanks, AK


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngToSearch As Range
Dim rngToFind As Range
Dim rngFound As Range
Dim wks As Worksheet
Set wks = ActiveSheet

Application.EnableEvents = True
With wks
Set rngToSearch = .Range("L:L")
End With
If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
If Target.Value > 0 Then

set rngFound = rngToSearch.Find(What:=Target.Offset(0, -2), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)

If Not rngFound Is Nothing Then
rngFound.Offset(0, 3).Value = Target.Value
else
MsgBox("Not Fond")
End If
End If
End Sub


"Paige" <Paige@discussions.microsoft.com> wrote in message
news:8A076544-2FFF-4B66-839E-271F0C1D29C5@microsoft.com...
> I've worked 5 hours on this and cannot get it to work; would appreciate
> knowing what is wrong. If the user makes a change to any cell AU10:AU30
and
> the entry is > 0, then I need to see if the offset (0,-2) to that entry in
> Col AU is anywhere in Column L. If it is, then the macro should place the
> target value just made in Col AU as an offset (0,3) to what is found in
Col L
> (there could be no instances found, 1, or many). Here is what I have,
which
> doesn't work; no error messages, it just doesn't work:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rngToSearch As Range
> Dim rngToFind As Range
> Dim rngFound As Range
> Dim rng As Range
> Dim wks As Worksheet
> Set wks = ActiveSheet
>
> Application.EnableEvents = True
> With wks
> Set rngToSearch = .Columns("L")
> Set rngToFind = Target.Offset(0, -2)
> End With
> If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> If Target.Value > 0 Then
> For Each rng In rngToSearch
> Set rngFound = rngToSearch.Find(What:=Target.Offset(0, -2), _
> LookIn:=xlFormulas, _
> LookAt:=xlWhole, _
> MatchCase:=False)
> If rngFound Is Nothing Then rng.Offset(0, 3).Value = Target.Value
> Next rng
> End If
> End If
> End Sub

OPPS. The sentence should read. "Need not use a For each loop".
Sorry, Rick


"Rick Hansen" <rlhansen@alaska.net> wrote in message
news:OhpWCHKZGHA.4884@TK2MSFTNGP02.phx.gbl...
> Good Morning Paige,
>
> I reviewed your code and made a few changes. When using the find method
on
> a range you need to use a For each Loop. The Find method will search the
> complete range. So I made a few changes to your code. It has not been
test,
> but doing what your looking for. Good in your VBA Code..
>
>
> Rick, Fairbanks, AK
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rngToSearch As Range
> Dim rngToFind As Range
> Dim rngFound As Range
> Dim wks As Worksheet
> Set wks = ActiveSheet
>
> Application.EnableEvents = True
> With wks
> Set rngToSearch = .Range("L:L")
> End With
> If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> If Target.Value > 0 Then
>
> set rngFound = rngToSearch.Find(What:=Target.Offset(0, -2), _
> LookIn:=xlFormulas, _
> LookAt:=xlWhole, _
> MatchCase:=False)
>
> If Not rngFound Is Nothing Then
> rngFound.Offset(0, 3).Value = Target.Value
> else
> MsgBox("Not Fond")
> End If
> End If
> End Sub
>
>
> "Paige" <Paige@discussions.microsoft.com> wrote in message
> news:8A076544-2FFF-4B66-839E-271F0C1D29C5@microsoft.com...
> > I've worked 5 hours on this and cannot get it to work; would appreciate
> > knowing what is wrong. If the user makes a change to any cell AU10:AU30
> and
> > the entry is > 0, then I need to see if the offset (0,-2) to that entry
in
> > Col AU is anywhere in Column L. If it is, then the macro should place
the
> > target value just made in Col AU as an offset (0,3) to what is found in
> Col L
> > (there could be no instances found, 1, or many). Here is what I have,
> which
> > doesn't work; no error messages, it just doesn't work:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim rngToSearch As Range
> > Dim rngToFind As Range
> > Dim rngFound As Range
> > Dim rng As Range
> > Dim wks As Worksheet
> > Set wks = ActiveSheet
> >
> > Application.EnableEvents = True
> > With wks
> > Set rngToSearch = .Columns("L")
> > Set rngToFind = Target.Offset(0, -2)
> > End With
> > If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> > If Target.Value > 0 Then
> > For Each rng In rngToSearch
> > Set rngFound = rngToSearch.Find(What:=Target.Offset(0, -2),
_
> > LookIn:=xlFormulas, _
> > LookAt:=xlWhole, _
> > MatchCase:=False)
> > If rngFound Is Nothing Then rng.Offset(0, 3).Value =
Target.Value
> > Next rng
> > End If
> > End If
> > End Sub
>
>

Thanks, Rick. It works, except that it doesn't keep going down Column L to
see if there are any more that match target.offset(0,-2) [Col AU], and if so,
enter the same value. Any ideas?

"Rick Hansen" wrote:

> OPPS. The sentence should read. "Need not use a For each loop".
> Sorry, Rick
>
>
> "Rick Hansen" <rlhansen@alaska.net> wrote in message
> news:OhpWCHKZGHA.4884@TK2MSFTNGP02.phx.gbl...
> > Good Morning Paige,
> >
> > I reviewed your code and made a few changes. When using the find method
> on
> > a range you need to use a For each Loop. The Find method will search the
> > complete range. So I made a few changes to your code. It has not been
> test,
> > but doing what your looking for. Good in your VBA Code..
> >
> >
> > Rick, Fairbanks, AK
> >
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim rngToSearch As Range
> > Dim rngToFind As Range
> > Dim rngFound As Range
> > Dim wks As Worksheet
> > Set wks = ActiveSheet
> >
> > Application.EnableEvents = True
> > With wks
> > Set rngToSearch = .Range("L:L")
> > End With
> > If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> > If Target.Value > 0 Then
> >
> > set rngFound = rngToSearch.Find(What:=Target.Offset(0, -2), _
> > LookIn:=xlFormulas, _
> > LookAt:=xlWhole, _
> > MatchCase:=False)
> >
> > If Not rngFound Is Nothing Then
> > rngFound.Offset(0, 3).Value = Target.Value
> > else
> > MsgBox("Not Fond")
> > End If
> > End If
> > End Sub
> >
> >
> > "Paige" <Paige@discussions.microsoft.com> wrote in message
> > news:8A076544-2FFF-4B66-839E-271F0C1D29C5@microsoft.com...
> > > I've worked 5 hours on this and cannot get it to work; would appreciate
> > > knowing what is wrong. If the user makes a change to any cell AU10:AU30
> > and
> > > the entry is > 0, then I need to see if the offset (0,-2) to that entry
> in
> > > Col AU is anywhere in Column L. If it is, then the macro should place
> the
> > > target value just made in Col AU as an offset (0,3) to what is found in
> > Col L
> > > (there could be no instances found, 1, or many). Here is what I have,
> > which
> > > doesn't work; no error messages, it just doesn't work:
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim rngToSearch As Range
> > > Dim rngToFind As Range
> > > Dim rngFound As Range
> > > Dim rng As Range
> > > Dim wks As Worksheet
> > > Set wks = ActiveSheet
> > >
> > > Application.EnableEvents = True
> > > With wks
> > > Set rngToSearch = .Columns("L")
> > > Set rngToFind = Target.Offset(0, -2)
> > > End With
> > > If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> > > If Target.Value > 0 Then
> > > For Each rng In rngToSearch
> > > Set rngFound = rngToSearch.Find(What:=Target.Offset(0, -2),
> _
> > > LookIn:=xlFormulas, _
> > > LookAt:=xlWhole, _
> > > MatchCase:=False)
> > > If rngFound Is Nothing Then rng.Offset(0, 3).Value =
> Target.Value
> > > Next rng
> > > End If
> > > End If
> > > End Sub
> >
> >
>
>
>

Good Afternoon Paige,
I modifed the Code so it will now find all like item(s) in the search
Range. If you have any more question let me know. Good Luck again...

Rick


"Paige" <Paige@discussions.microsoft.com> wrote in message
news:2A70064F-A8BB-431D-A66D-F31C969A221F@microsoft.com...
> Thanks, Rick. It works, except that it doesn't keep going down Column L
to
> see if there are any more that match target.offset(0,-2) [Col AU], and if
so,
> enter the same value. Any ideas?
>
> "Rick Hansen" wrote:
>
> > OPPS. The sentence should read. "Need not use a For each loop".
> > Sorry, Rick
> >
> >
> > "Rick Hansen" <rlhansen@alaska.net> wrote in message
> > news:OhpWCHKZGHA.4884@TK2MSFTNGP02.phx.gbl...
> > > Good Morning Paige,
> > >
> > > I reviewed your code and made a few changes. When using the find
method
> > on
> > > a range you need to use a For each Loop. The Find method will search
the
> > > complete range. So I made a few changes to your code. It has not been
> > test,
> > > but doing what your looking for. Good in your VBA Code..
> > >
> > >
> > > Rick, Fairbanks, AK
> > >
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim rngToSearch As Range
> > > Dim rngToFind As Range
> > > Dim rngFound As Range
> > > Dim wks As Worksheet
> > > Set wks = ActiveSheet
> > >
> > > Application.EnableEvents = True
> > > With wks
> > > Set rngToSearch = .Range("L:L")
> > > End With
> > > If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> > > If Target.Value > 0 Then
> > >
> > > set rngFound = rngToSearch.Find(What:=Target.Offset(0, -2), _
> > > LookIn:=xlFormulas, _
> > > LookAt:=xlWhole, _
> > > MatchCase:=False)
> > >
> > > If Not rngFound Is Nothing Then
> > > rngFound.Offset(0, 3).Value = Target.Value
> > > else
> > > MsgBox("Not Fond")
> > > End If
> > > End If
> > > End Sub
> > >
> > >
> > > "Paige" <Paige@discussions.microsoft.com> wrote in message
> > > news:8A076544-2FFF-4B66-839E-271F0C1D29C5@microsoft.com...
> > > > I've worked 5 hours on this and cannot get it to work; would
appreciate
> > > > knowing what is wrong. If the user makes a change to any cell
AU10:AU30
> > > and
> > > > the entry is > 0, then I need to see if the offset (0,-2) to that
entry
> > in
> > > > Col AU is anywhere in Column L. If it is, then the macro should
place
> > the
> > > > target value just made in Col AU as an offset (0,3) to what is found
in
> > > Col L
> > > > (there could be no instances found, 1, or many). Here is what I
have,
> > > which
> > > > doesn't work; no error messages, it just doesn't work:
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > Dim rngToSearch As Range
> > > > Dim rngToFind As Range
> > > > Dim rngFound As Range
> > > > Dim rng As Range
> > > > Dim wks As Worksheet
> > > > Set wks = ActiveSheet
> > > >
> > > > Application.EnableEvents = True
> > > > With wks
> > > > Set rngToSearch = .Columns("L")
> > > > Set rngToFind = Target.Offset(0, -2)
> > > > End With
> > > > If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> > > > If Target.Value > 0 Then
> > > > For Each rng In rngToSearch
> > > > Set rngFound =
rngToSearch.Find(What:=Target.Offset(0, -2),
> > _
> > > > LookIn:=xlFormulas, _
> > > > LookAt:=xlWhole, _
> > > > MatchCase:=False)
> > > > If rngFound Is Nothing Then rng.Offset(0, 3).Value =
> > Target.Value
> > > > Next rng
> > > > End If
> > > > End If
> > > > End Sub
> > >
> > >
> >
> >
> >

Sorry I didn't Paste the Code.. :~(

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngToSearch As Range
Dim rngToFind As Range
Dim rngFound As Range
Dim wks As Worksheet
Set wks = ActiveSheet
FirstAdd as String

Application.EnableEvents = True
With wks
Set rngToSearch = .Range("L:L")
End With
If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
If Target.Value > 0 Then

set rngFound = rngToSearch.Find(What:=Target.Offset(0, -2), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
if Not rngFound is Nothing Then
FirstAdd = rngFound.Address
Do
rngFound.Offset(0, 3).Value = Target.Value
Set rngFound = rngToSearch.FindNext(rngFound)
Loop While (FirstAdd <> rngFound.Address)
else
MsgBox("Not Found")
End If
End If
End Sub



"Paige" <Paige@discussions.microsoft.com> wrote in message
news:2A70064F-A8BB-431D-A66D-F31C969A221F@microsoft.com...
> Thanks, Rick. It works, except that it doesn't keep going down Column L
to
> see if there are any more that match target.offset(0,-2) [Col AU], and if
so,
> enter the same value. Any ideas?
>
> "Rick Hansen" wrote:
>
> > OPPS. The sentence should read. "Need not use a For each loop".
> > Sorry, Rick
> >
> >
> > "Rick Hansen" <rlhansen@alaska.net> wrote in message
> > news:OhpWCHKZGHA.4884@TK2MSFTNGP02.phx.gbl...
> > > Good Morning Paige,
> > >
> > > I reviewed your code and made a few changes. When using the find
method
> > on
> > > a range you need to use a For each Loop. The Find method will search
the
> > > complete range. So I made a few changes to your code. It has not been
> > test,
> > > but doing what your looking for. Good in your VBA Code..
> > >
> > >
> > > Rick, Fairbanks, AK
> > >
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim rngToSearch As Range
> > > Dim rngToFind As Range
> > > Dim rngFound As Range
> > > Dim wks As Worksheet
> > > Set wks = ActiveSheet
> > >
> > > Application.EnableEvents = True
> > > With wks
> > > Set rngToSearch = .Range("L:L")
> > > End With
> > > If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> > > If Target.Value > 0 Then
> > >
> > > set rngFound = rngToSearch.Find(What:=Target.Offset(0, -2), _
> > > LookIn:=xlFormulas, _
> > > LookAt:=xlWhole, _
> > > MatchCase:=False)
> > >
> > > If Not rngFound Is Nothing Then
> > > rngFound.Offset(0, 3).Value = Target.Value
> > > else
> > > MsgBox("Not Fond")
> > > End If
> > > End If
> > > End Sub
> > >
> > >
> > > "Paige" <Paige@discussions.microsoft.com> wrote in message
> > > news:8A076544-2FFF-4B66-839E-271F0C1D29C5@microsoft.com...
> > > > I've worked 5 hours on this and cannot get it to work; would
appreciate
> > > > knowing what is wrong. If the user makes a change to any cell
AU10:AU30
> > > and
> > > > the entry is > 0, then I need to see if the offset (0,-2) to that
entry
> > in
> > > > Col AU is anywhere in Column L. If it is, then the macro should
place
> > the
> > > > target value just made in Col AU as an offset (0,3) to what is found
in
> > > Col L
> > > > (there could be no instances found, 1, or many). Here is what I
have,
> > > which
> > > > doesn't work; no error messages, it just doesn't work:
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > Dim rngToSearch As Range
> > > > Dim rngToFind As Range
> > > > Dim rngFound As Range
> > > > Dim rng As Range
> > > > Dim wks As Worksheet
> > > > Set wks = ActiveSheet
> > > >
> > > > Application.EnableEvents = True
> > > > With wks
> > > > Set rngToSearch = .Columns("L")
> > > > Set rngToFind = Target.Offset(0, -2)
> > > > End With
> > > > If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> > > > If Target.Value > 0 Then
> > > > For Each rng In rngToSearch
> > > > Set rngFound =
rngToSearch.Find(What:=Target.Offset(0, -2),
> > _
> > > > LookIn:=xlFormulas, _
> > > > LookAt:=xlWhole, _
> > > > MatchCase:=False)
> > > > If rngFound Is Nothing Then rng.Offset(0, 3).Value =
> > Target.Value
> > > > Next rng
> > > > End If
> > > > End If
> > > > End Sub
> > >
> > >
> >
> >
> >

Paige, if your interested, here the code using the For Each Loop, that
accomplishes same thing as using the find, and FindNext methods in the
other code. Made a change in selecting the range to search. See the code
below.

Rick



Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngToSearch As Range
Dim rng As Range
Dim wks As Worksheet
Dim LastRow As Long

Set wks = ActiveSheet
Application.EnableEvents = True
With wks
LastRow = .Range("L2").End(xlDown).Row '<- Change begin cell of range here
Set rngToSearch = .Range("L2:L" & LastRow)

End With
If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
If Target.Value > 0 Then
For Each rng In rngToSearch
If rng.Value = Target.Offset(0, -2).Value Then
rng.Offset(0, 3).Value = Target.Value
End If
Next rng
End If
End If
End Sub


"Paige" <Paige@discussions.microsoft.com> wrote in message
news:2A70064F-A8BB-431D-A66D-F31C969A221F@microsoft.com...
> Thanks, Rick. It works, except that it doesn't keep going down Column L
to
> see if there are any more that match target.offset(0,-2) [Col AU], and if
so,
> enter the same value. Any ideas?
>
> "Rick Hansen" wrote:
>
> > OPPS. The sentence should read. "Need not use a For each loop".
> > Sorry, Rick
> >
> >
> > "Rick Hansen" <rlhansen@alaska.net> wrote in message
> > news:OhpWCHKZGHA.4884@TK2MSFTNGP02.phx.gbl...
> > > Good Morning Paige,
> > >
> > > I reviewed your code and made a few changes. When using the find
method
> > on
> > > a range you need to use a For each Loop. The Find method will search
the
> > > complete range. So I made a few changes to your code. It has not been
> > test,
> > > but doing what your looking for. Good in your VBA Code..
> > >
> > >
> > > Rick, Fairbanks, AK
> > >
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim rngToSearch As Range
> > > Dim rngToFind As Range
> > > Dim rngFound As Range
> > > Dim wks As Worksheet
> > > Set wks = ActiveSheet
> > >
> > > Application.EnableEvents = True
> > > With wks
> > > Set rngToSearch = .Range("L:L")
> > > End With
> > > If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> > > If Target.Value > 0 Then
> > >
> > > set rngFound = rngToSearch.Find(What:=Target.Offset(0, -2), _
> > > LookIn:=xlFormulas, _
> > > LookAt:=xlWhole, _
> > > MatchCase:=False)
> > >
> > > If Not rngFound Is Nothing Then
> > > rngFound.Offset(0, 3).Value = Target.Value
> > > else
> > > MsgBox("Not Fond")
> > > End If
> > > End If
> > > End Sub
> > >
> > >
> > > "Paige" <Paige@discussions.microsoft.com> wrote in message
> > > news:8A076544-2FFF-4B66-839E-271F0C1D29C5@microsoft.com...
> > > > I've worked 5 hours on this and cannot get it to work; would
appreciate
> > > > knowing what is wrong. If the user makes a change to any cell
AU10:AU30
> > > and
> > > > the entry is > 0, then I need to see if the offset (0,-2) to that
entry
> > in
> > > > Col AU is anywhere in Column L. If it is, then the macro should
place
> > the
> > > > target value just made in Col AU as an offset (0,3) to what is found
in
> > > Col L
> > > > (there could be no instances found, 1, or many). Here is what I
have,
> > > which
> > > > doesn't work; no error messages, it just doesn't work:
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > Dim rngToSearch As Range
> > > > Dim rngToFind As Range
> > > > Dim rngFound As Range
> > > > Dim rng As Range
> > > > Dim wks As Worksheet
> > > > Set wks = ActiveSheet
> > > >
> > > > Application.EnableEvents = True
> > > > With wks
> > > > Set rngToSearch = .Columns("L")
> > > > Set rngToFind = Target.Offset(0, -2)
> > > > End With
> > > > If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> > > > If Target.Value > 0 Then
> > > > For Each rng In rngToSearch
> > > > Set rngFound =
rngToSearch.Find(What:=Target.Offset(0, -2),
> > _
> > > > LookIn:=xlFormulas, _
> > > > LookAt:=xlWhole, _
> > > > MatchCase:=False)
> > > > If rngFound Is Nothing Then rng.Offset(0, 3).Value =
> > Target.Value
> > > > Next rng
> > > > End If
> > > > End If
> > > > End Sub
> > >
> > >
> >
> >
> >

Thanks, Rick!!!! Sorry for taking up so much of your time, but really
appreciate the assistance!

"Rick Hansen" wrote:

> Paige, if your interested, here the code using the For Each Loop, that
> accomplishes same thing as using the find, and FindNext methods in the
> other code. Made a change in selecting the range to search. See the code
> below.
>
> Rick
>
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rngToSearch As Range
> Dim rng As Range
> Dim wks As Worksheet
> Dim LastRow As Long
>
> Set wks = ActiveSheet
> Application.EnableEvents = True
> With wks
> LastRow = .Range("L2").End(xlDown).Row '<- Change begin cell of range here
> Set rngToSearch = .Range("L2:L" & LastRow)
>
> End With
> If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> If Target.Value > 0 Then
> For Each rng In rngToSearch
> If rng.Value = Target.Offset(0, -2).Value Then
> rng.Offset(0, 3).Value = Target.Value
> End If
> Next rng
> End If
> End If
> End Sub
>
>
> "Paige" <Paige@discussions.microsoft.com> wrote in message
> news:2A70064F-A8BB-431D-A66D-F31C969A221F@microsoft.com...
> > Thanks, Rick. It works, except that it doesn't keep going down Column L
> to
> > see if there are any more that match target.offset(0,-2) [Col AU], and if
> so,
> > enter the same value. Any ideas?
> >
> > "Rick Hansen" wrote:
> >
> > > OPPS. The sentence should read. "Need not use a For each loop".
> > > Sorry, Rick
> > >
> > >
> > > "Rick Hansen" <rlhansen@alaska.net> wrote in message
> > > news:OhpWCHKZGHA.4884@TK2MSFTNGP02.phx.gbl...
> > > > Good Morning Paige,
> > > >
> > > > I reviewed your code and made a few changes. When using the find
> method
> > > on
> > > > a range you need to use a For each Loop. The Find method will search
> the
> > > > complete range. So I made a few changes to your code. It has not been
> > > test,
> > > > but doing what your looking for. Good in your VBA Code..
> > > >
> > > >
> > > > Rick, Fairbanks, AK
> > > >
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > Dim rngToSearch As Range
> > > > Dim rngToFind As Range
> > > > Dim rngFound As Range
> > > > Dim wks As Worksheet
> > > > Set wks = ActiveSheet
> > > >
> > > > Application.EnableEvents = True
> > > > With wks
> > > > Set rngToSearch = .Range("L:L")
> > > > End With
> > > > If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> > > > If Target.Value > 0 Then
> > > >
> > > > set rngFound = rngToSearch.Find(What:=Target.Offset(0, -2), _
> > > > LookIn:=xlFormulas, _
> > > > LookAt:=xlWhole, _
> > > > MatchCase:=False)
> > > >
> > > > If Not rngFound Is Nothing Then
> > > > rngFound.Offset(0, 3).Value = Target.Value
> > > > else
> > > > MsgBox("Not Fond")
> > > > End If
> > > > End If
> > > > End Sub
> > > >
> > > >
> > > > "Paige" <Paige@discussions.microsoft.com> wrote in message
> > > > news:8A076544-2FFF-4B66-839E-271F0C1D29C5@microsoft.com...
> > > > > I've worked 5 hours on this and cannot get it to work; would
> appreciate
> > > > > knowing what is wrong. If the user makes a change to any cell
> AU10:AU30
> > > > and
> > > > > the entry is > 0, then I need to see if the offset (0,-2) to that
> entry
> > > in
> > > > > Col AU is anywhere in Column L. If it is, then the macro should
> place
> > > the
> > > > > target value just made in Col AU as an offset (0,3) to what is found
> in
> > > > Col L
> > > > > (there could be no instances found, 1, or many). Here is what I
> have,
> > > > which
> > > > > doesn't work; no error messages, it just doesn't work:
> > > > >
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > Dim rngToSearch As Range
> > > > > Dim rngToFind As Range
> > > > > Dim rngFound As Range
> > > > > Dim rng As Range
> > > > > Dim wks As Worksheet
> > > > > Set wks = ActiveSheet
> > > > >
> > > > > Application.EnableEvents = True
> > > > > With wks
> > > > > Set rngToSearch = .Columns("L")
> > > > > Set rngToFind = Target.Offset(0, -2)
> > > > > End With
> > > > > If Not (Intersect(Target, Range("AU10:AU30")) Is Nothing) Then
> > > > > If Target.Value > 0 Then
> > > > > For Each rng In rngToSearch
> > > > > Set rngFound =
> rngToSearch.Find(What:=Target.Offset(0, -2),
> > > _
> > > > > LookIn:=xlFormulas, _
> > > > > LookAt:=xlWhole, _
> > > > > MatchCase:=False)
> > > > > If rngFound Is Nothing Then rng.Offset(0, 3).Value =
> > > Target.Value
> > > > > Next rng
> > > > > End If
> > > > > End If
> > > > > End Sub
> > > >
> > > >
> > >
> > >
> > >
>
>
>