Need Row number at run time vba

Question

I want row number dynamic by finding "Company Name"

I want to create a macro which should update new table by using data from below table.

Right now I am updating Sheet1.Cells(421, i - 1) manually. But I want it to be dynamic.

Here Row number 421 is "Company1" after "Booking $".

Please help to make code dynamic

Sub UpdateBookings()

    x = Sheet1.Range("D1").Value

    For i = 3 To 15
        Sheet2.Cells(4, i) = (Sheet1.Cells(420, i - 1).Value * 1000) / x
    Next i

End Sub

enter image description here

I'd suggest that you make use of the [Range.Find](https://msdn.microsoft.com/VBA/Excel-VBA/articles/range-find-method-excel) method to search for your company name and thus making your code dynamic. The sample code posted in the above website by Microsoft should be sufficient to get you start. Please remember that this website is not offering free code-writing services but rather helps you write your own code. So, I hope the above link is sufficient to keep you going. Don't hesitate to come back if you are running into more / different problems. — Ralph, Sep 19 '17 at 09:04
Possible duplicate of [How to find a value in an excel column by vba code Cells.Find](https://stackoverflow.com/questions/14931700/how-to-find-a-value-in-an-excel-column-by-vba-code-cells-find) — shA.t, Sep 19 '17 at 09:04
Try `rowIndex = Sheet1.Columns(1).Find("Company1").Row` to get the row number of cell with value `Company1` in `Column 1`. — Mrig, Sep 19 '17 at 09:05

score 1 · Accepted Answer · answered Sep 19 '17 at 10:05

Try the code below, explanation inside the code's comments:

Option Explicit

Sub UpdateBookings()

Dim i As Long, FindRng As Range, RowStart As Long, x

With Sheet1
    x = .Range("D1").Value

    Set FindRng = .Cells.Find(What:="Company1", LookIn:=xlValues, LookAt:=xlWhole)
                                ' found an empty cell in the specified range
    If Not FindRng Is Nothing Then ' Find was successfull
        RowStart = FindRng.Row
    Else ' Find failed
        MsgBox "Unable to find Company1"
        Exit Sub
    End If    

    For i = 3 To 15
        Sheet2.Cells(4, i) = (.Cells(RowStart, i - 1).Value * 1000) / x
    Next i

End With

End Sub

Need Row number at run time vba

1 Answers1