# 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;
=MATCH(22,\$A\$1:\$A\$200,0)

• Re: Find Value And Return Row Number

stickyfeet,

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

Regards,

Robert

• Re: Find Value And Return Row Number

or another way

Code
``````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!)

Brilliant.

Thanks again

• Re: Find Value And Return Row Number

Code
``````Sub ReturnRowNumber()
Range("A65536").End(xlUp).Select
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.