Excel vba - how to use a variable inside the range function?

Question

How do I make this a function so that I can pass "B38" as a variable? I tried putting 'startCell' inside the range function, but it barfed and I'm not clear what's required to make that work...

TIA

Set startCell = Range("B38")
For i = 0 To 6
    hourVal = Worksheets("Expense & Hours").Range("b38").Offset(0, i).Value
    dateVal = Worksheets("Expense & Hours").Range("b38").Offset(2, i).Value
    MsgBox "Current: " & hourVal & "  " & dateVal
Next i

score 1 · Accepted Answer · answered Jun 15 '21 at 20:03

You mean like this?

Dim addr As String

addr = "B38"

Set startCell = Worksheets("Expense & Hours").Range(addr)
For i = 0 To 6
    hourVal = startCell.Offset(0, i).Value
    dateVal = startCell.Offset(2, i).Value
    MsgBox "Current: " & hourVal & "  " & dateVal
Next i

Excel vba - how to use a variable inside the range function?

1 Answers1