Excel VBA - get cell row for a range from another cell value

Question

I have the following code:

Sub Button7_Click()
Worksheets("Contracts").Range("AI2").Value = _
InputBox(Prompt:="registrera kontrakts-id.")
End Sub

When using the code above, cell AI2 is updated on the "contract" sheet.

But, I need to fetch the row number from a value in cell C2 of the current sheet.

I tried something like this, but it doesn't work:

Worksheets("Contracts").Range("AI" & C2).Value = _

How can I get the "range" to use static column id (AI) but pick the row from cell C2?

Vityata · Accepted Answer · 2023-04-12T08:04:41.157

There are quite a few ways to do it, probably the closest one to your question is this one:

Sub Button7_Click()
    dim myRow as long
    myRow = ActiveSheet.Range("C2")
    ActiveSheet.Range("AI" & myRow).Value = _
    InputBox(Prompt:="registrera kontrakts-id.")
End Sub

Another one is ActiveSheet.Cells(myRow, "AI").Value.

In this scenario it is absolutely ok to use it, but in many others ActiveSheet is something to avoid.

In general, some "sanity" check, whether the value in Range("C2") is valid could be useful, as far as if it is empty, text, negative number or zero an error would occur.

Sub Button7_Click()

    Dim myRow As Long
    myRow = ActiveSheet.Range("C2")
    
    If Not IsNumeric(myRow) Then
        MsgBox "C2 is not numeric!"
        Exit Sub
    ElseIf myRow <= 0 Then
        MsgBox "C2 is smaller or equal than 0!"
        Exit Sub
    End If
    
    ActiveSheet.Range("AI" & myRow).Value = InputBox(Prompt:="registrera kontrakts-id.")
    
End Sub

Excel VBA - get cell row for a range from another cell value

1 Answers1