This code comes from another post I had help with...
1) Original
2) Second edit
A new problem I've found is that .FIND does not find 9,100 when searching for 9100, due to different number formats, e.g. Number vs General.
I tried a couple of things, that busted the code. Seems simple, but I'm not finding an answer online.
1) How can I make it find the same number in a format?
Summary of what the code does:
When triggered by entering a "y" in col G, the code will retrieve the value on the same row in either col d or E. Then it will search col K for the same value and if found, make that cell the active cell.
This is the basic working code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rgG As Range
Dim rgK As Range
Dim cSearch As Range
Dim lLoop As Long
Dim cResult As Range
Dim rgXsct As Range
'Find the last used rows in Cols G & K
Dim lrowG As Long
Dim lrowK As Long
lrowG = Columns("G:G").Find(What:="*", _
After:=Range("G1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
'Debug.Print lrowG
lrowK = Columns("K:K").Find(What:="*", _
After:=Range("K1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
'Debug.Print lrowK
Set rgG = Range("g5:g" & lrowG)
Set rgK = Range("k6:k" & lrowK)
Set rgXsct = Intersect(Target, rgG) '"Intersect" will ensure the current cell lies on the correct column.
'*********** IF CONDITIONS to run. ****************
'1) Columns.count & Row.count = 1 confirms only one cell selected (not a range of cells). -- I think
'2) LCase(Target.Text) = "y" makes the Target lowcase and compares it.
'3) Intersect makes sure the Target is in the relevant range.
'4)
If Target.Columns.Count = 1 And Target.Rows.Count = 1 And _
LCase(Target.Text) = "y" And _
Not rgXsct Is Nothing Then
Application.ScreenUpdating = False
Set cSearch = Target.Offset(0, -3) 'Returns value in Col D
If cSearch = 0 Or cSearch = "" Then Set cSearch = Target.Offset(0, -2) 'If nothing in D, check E.
If cSearch = 0 Or cSearch = "" Then GoTo end_search 'If nothing in E, end.
'Debug.Print "Target "; Target
'Debug.Print "cSearch.value "; cSearch.Value
'This will loop through the column to search, to find the value identified above, and return its cell address.
With rgK
Set cResult = .Cells(1, 1)
'For lLoop = 1 To WorksheetFunction.CountIf(.Cells, cSearch.Value)
Set cResult = .Find(What:=cSearch.Value, _
After:=cResult, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
'Debug.Print "cResult " & cResult
If Not cResult Is Nothing Then
MsgBox "val: " & cSearch.Value & " Matching Cell: " & cResult.Address
'Use the cell address identified above to move the active cell to that address.
Cells(Range(cResult.Address).Row, Range(cResult.Address).Column).Select 'Have to convert the address to row/column to use in Cell.Select.
Else
MsgBox (cSearch.Value & " not found.")
GoTo end_search
End If
'Next lLoop
End With
End If
end_search:
'Application.ScreenUpdating = True
'End Sub
2) Can you tell me the value of that commented out For loop after the with? It was suggested as a solution to make this overall code work, but it works without it.
3) How can I make this more concise? It seems complex. For instance, why do I need a with statement, when Columns(K:K).Find(... without a with statement should work based on what I've read (but it does not in this module).
