VBA how to change a range variable using something like offset

Question

I'd think that the following code should produce a diagonal of numbers, and I am wondering why this code doesn't work as intended:

Sub RangeExample()

Dim a As Range

Set a = Sheets(1).Range("a1")

a.Value = 0

Dim i As Integer


    For i = 1 To 100

        a = a.Offset(1, 1)
        a.Value = i

    Next i

End Sub

I understand there are many ways of producing a diagonal of numbers, I'm not asking how to do that.
What I'm asking is how I would change my range variable a to become a different range, and do that iteratively. It seems to me that as a.offset(1,1) returns a range object that's one over and one down, I should be able to reassign a as this new range, assign a value, and move on.

You're missing `Set`: `Set a = a.Offset(1, 1)`. – BigBen Sep 25 '19 at 21:27 — BigBen, Sep 25 '19 at 21:27

BigBen · Accepted Answer · 2019-09-25T21:37:40.517

2

Your current issue is that you're missing a Set:

Set a = a.Offset(1, 1)

Note that you could also just use i and not reSet:

a.Offset(i, i).Value = i

Another option is to use Cells, e.g.

Sheets(1).Cells(i + 1, i + 1).Value = i

There's more than one way to skin a cat - pick whatever is easiest and most intuitive to future you.

edited Sep 25 '19 at 21:37

answered Sep 25 '19 at 21:31

BigBen

46,229
7
24
40

score 0 · Answer 2 · answered Sep 25 '19 at 21:40

Thanks for the answer, I didn't know set was required in this case. The specific answer I was looking for I have now found at:

What does the keyword Set actually do in VBA?

Specifically, the following answer by LeppyR64. "Set is used for setting object references, as opposed to assigning a value."

I didn't know that equality alone only impacted the value of the range object a. To actually change the range a was referencing, I needed set because a is supposed to refer to a new range object.

score 0 · Answer 3 · answered Sep 26 '19 at 05:48

the issue has already been addressed by @BigBen

but you could avoid re-setting the range at every iteration by means of With...End With block

Option Explicit

Sub RangeExample()
    Dim i As Long

    With Sheets(1).Range("a1") ' reference topleftmost cell
        .Value = 0 ' write referenced cell value
        For i = 1 To 100
            .Offset(i, i).Value = i 'write referenced cell current offset value
        Next
    End With
End Sub

VBA how to change a range variable using something like offset

3 Answers3