Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find then offset. | Excel Worksheet Functions | |||
search column, hyperlink, offset, substitute, match (omg) | Excel Discussion (Misc queries) | |||
search and offset? | Excel Programming | |||
macro to search and replace with offset | Excel Discussion (Misc queries) | |||
HOW TO USE FIND OR SEARCH FUNCTION TO FILL DATA | Excel Worksheet Functions |