capture/retrieve row number from cell address of FIND match

R

r121a947

New member
Joined
Jun 29, 2019
Messages
49
Reaction score
0
Points
0
Excel Version(s)
Office 365
Is there a way to capture/retrieve the row number from the cell address of a FIND match?

I have tried numerous ways, using various string functions, but always get a "type mismatch" when running it.

Thank you.
 
Here is VBA code to use with the following layout

Code: 
Option Explicit


Sub FindN()
    Dim i As Long, lr As Long, crit As Variant
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set crit = Range("D1")
    For i = 2 To lr
        If Range("A" & i) = crit Then
            MsgBox ("Address Found is " & Range("A" & i).Address)
        End If
    Next i
End Sub

vABCD
1NumberSearch Criteria15
23
34
45
56
67
78
89
910
1011
1112
1213
1314
1415
1516
1617
1718
1819
1920
2021
2122
2223
2324
2425
2526
2627
 
How about
Code: 
Dim Fnd As Range
Set Fnd = Range("A:A").Find("something", , , xlWhole, , , False, , False)
If Not Fnd Is Nothing Then MsgBox "Row is " & Fnd.Row
or if it's the activecell then
Code: 
ActiveCell.row
 
Thank you.

I will try all suggestions and let you know how things go.
 
ActiveCell.Row seems to work.

I think I might have a working version of my project.

Thanks for everyone's help.
 
You're welcome & thanks for the feedback
 
You must log in or register to reply here.
Back
Top