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

Tim Williams · Accepted Answer · 2021-06-15 20:03:20Z

2

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

answered Jun 15, 2021 at 20:03

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related