Find Value And Return Row Number

Re: Finding A Value And Returning Its Row Number

Sub GetRowNum()
Dim lRow As Long


    On Error Resume Next
    lRow = Application.WorksheetFunction.Match(22, Range("A1:A200"), 0)
    On Error GoTo 0
    
    If lRow > 0 Then
        'code
    
    
    End If


End Sub

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:

Sub Macro1()


Dim intMyVal As Integer
Dim lngLastRow As Long
Dim strRowNoList As String


intMyVal = 1 'Value to search for, change as required.
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Search Column A, change as required.


For Each cell In Range("A2:A" & lngLastRow) 'Starting cell is A2, change as required.


    If cell.Value = intMyVal Then
        If strRowNoList = "" Then
        strRowNoList = strRowNoList & cell.Row
        Else
        strRowNoList = strRowNoList & ", " & cell.Row
        End If
    End If
Next cell


MsgBox strRowNoList


End Sub

Regards,

Robert

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

Re: Find Value And Return Row Number

Sub ReturnRowNumber()
    Range("A65536").End(xlUp).Select
    RowNumber = ActiveCell.Row
    MsgBox RowNumber
End Sub

Re: Find Value And Return Row Number

Quote from Trebor76;418783

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:

Code

Sub Macro1()


Dim intMyVal As Integer
Dim lngLastRow As Long
Dim strRowNoList As String


intMyVal = 1 'Value to search for, change as required.
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Search Column A, change as required.


For Each cell In Range("A2:A" & lngLastRow) 'Starting cell is A2, change as required.


    If cell.Value = intMyVal Then
        If strRowNoList = "" Then
        strRowNoList = strRowNoList & cell.Row
        Else
        strRowNoList = strRowNoList & ", " & cell.Row
        End If
    End If
Next cell


MsgBox strRowNoList


End Sub

Display More

Regards,

Robert

Display More

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.