Search, Find, and Fill in Offset

G

Guest

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
 
R

Rick Hansen

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
 
G

Guest

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

Rick Hansen

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 said:
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 said:
OPPS. The sentence should read. "Need not use a For each loop".
Sorry, Rick


method
on entry
in place
the rngToSearch.Find(What:=Target.Offset(0, -2),
_
Click to expand...
Click to expand...
 
R

Rick Hansen

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 said:
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 said:
OPPS. The sentence should read. "Need not use a For each loop".
Sorry, Rick


method
on entry
in place
the rngToSearch.Find(What:=Target.Offset(0, -2),
_
Click to expand...
Click to expand...
 
R

Rick Hansen

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 said:
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 said:
OPPS. The sentence should read. "Need not use a For each loop".
Sorry, Rick


method
on entry
in place
the rngToSearch.Find(What:=Target.Offset(0, -2),
_
Click to expand...
Click to expand...
 
G

Guest

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

Rick Hansen said:
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 said:
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 said:
OPPS. The sentence should read. "Need not use a For each loop".
Sorry, Rick


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


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find and loop help-multiple columns 3
Clear a "moving" range offset from Target 31
findnext error in loop 2
help with macro 10
search and offset? 2
find multiple values code tweak 5
Delete rows macro 3
Find next problem 1

Top