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

Search, Find, and Fill in Offset
 

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" wrote in message
...
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

Search, Find, and Fill in Offset
 

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


"Rick Hansen" wrote in message
...
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" wrote in message
...
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