Results 1 to 6 of 6

Thread: capture/retrieve row number from cell address of FIND match

  1. #1
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    20
    Articles
    0
    Excel Version
    Office 365

    capture/retrieve row number from cell address of FIND match



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    178
    Articles
    0
    Excel Version
    2019
    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
    v A B C D
    1 Number Search Criteria 15
    2 3
    3 4
    4 5
    5 6
    6 7
    7 8
    8 9
    9 10
    10 11
    11 12
    12 13
    13 14
    14 15
    15 16
    16 17
    17 18
    18 19
    19 20
    20 21
    21 22
    22 23
    23 24
    24 25
    25 26
    26 27

  3. #3
    Acolyte Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    82
    Articles
    0
    Excel Version
    O365
    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

  4. #4
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    20
    Articles
    0
    Excel Version
    Office 365
    Thank you.

    I will try all suggestions and let you know how things go.

  5. #5
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    20
    Articles
    0
    Excel Version
    Office 365
    ActiveCell.Row seems to work.

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

    Thanks for everyone's help.

  6. #6
    Acolyte Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    82
    Articles
    0
    Excel Version
    O365
    You're welcome & thanks for the feedback

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •