Find Value And Return Row Number

  • Hi

    I've been searching but without much joy.

    What I'm trying to do in vba is search for a number and then return what the row number is for that number.

    Hopefully someone can help.

    Many thanks

  • Re: Finding A Value And Returning Its Row Number

    Or, in a cell;

  • Re: Find Value And Return Row Number


    Though Dave has beaten me to the punch (and I'd say his solution is the way to go), here's my attempt nonetheless:



  • Re: Find Value And Return Row Number

    or another way

    Sub FindMyNubmer()
    Dim SearchRange As Range
    Dim FindRow As Range
    Set SearchRange = Range("A1", Range("A65536").End(xlUp))
    Set FindRow = SearchRange.Find(999, LookIn:=xlValues, lookat:=xlWhole)
    MsgBox FindRow.Row
    End Sub

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Re: Find Value And Return Row Number

    Gents thanks for such quick replies (this place really is the best).

    Spoilt for choice!

    Dave and Trebor both worked a treat. Bill didnt get the chance to try yours, but a thanks all the same .... I was struggling trying to find just the one way to get this to work (and spend a long time searching and trying different things!)


    Thanks again


  • Re: Find Value And Return Row Number

    Sub ReturnRowNumber()
        RowNumber = ActiveCell.Row
        MsgBox RowNumber
    End Sub
  • Re: Find Value And Return Row Number

    Hi Robert,

    This is a great code, i am very new to vba, i need help about the output.
    Right now the out put is a string with comma, how can i put print them as array or into cells, like A1,B1,C1,D1..., or A1, A2, A3, A4... many thanks in advance.

  • Re: Find Value And Return Row Number

    Welcome to Ozgrid, LynChen.

    The policy on this board is you do not post questions in threads started by other members.

    Please start your own thread, give it an accurate and concise title that summarises your issue and explain your issue fully. If you think this, or any other thread, can help clarify your issue you can include a link to it by copying the URL from the address bar of your browser and pasting into your message.

    Thank you.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!