Do more with
Sub test()
MsgBox (getRow(ActiveSheet.Range("B2:C5"), 2, 5))
MsgBox (getRow(ActiveSheet.Range("B2:C5"), 2, 10))
End Sub
Function getRow(rng As Range, theColumn as Long, theValue As Variant)
Dim r As Long
getRow = -1
For r = 1 To rng.Rows.Count
If rng.Cells(r, theColumn).Value = theValue Then
' Choose only one of the following assignments depending on what you want
' If you want the row with the range
getRow = r
' if you want the absolute row number in the worksheet
getRow = r + rng.Cells(1, 1).Row - 1
Exit Function
End If
Next r
End Function
If the range C2:C5 contains the values 1,3,5,7 then the first call in the test subroutine will return 4 (the absolute row) and the second will return -1.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A2"), Target) Is Nothing Then
ActiveSheet.Range("A4").Value = ActiveSheet.Columns("B:B").Find(Range("A2").Value).Row
End If
End Sub
Pretty quick
Premium Content
You need an Expert Office subscription to comment.Start Free Trial