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
> > >
> > >
> >
> >
> >