How to refer dynamicaly to an Excel cell with VBA Macro?

Question

I'm trying to make a loop through lines to read each time a cell, and write into another sheet.

Sub boucle_for()
    Dim col As Integer
    Dim lig As Integer
    Dim rr As Integer
    Dim cc As Integer

    col = 4
    lig = 3
    rr = 15
    cc = 24

    For i = 0 To 33
        Range("R[" & col & "]C[" & lig & "]").Formula = "=AVG('Weekly Changes'!R[" & rr & "]C[" & cc & "]:R[" & (rr + 1) & "]C[" & (cc + 1) & "])"
        rr = rr + 14
        lig = lig + 1
    Next
End Sub

My aim is to refer to a cell dynamically, but the VBA script says it's an error. I'm used to Java and JavaScript arrays, but I'm failing at this.

Where did I make a mistake?

LimaNightHawk · Accepted Answer · 2015-08-10 16:29:54Z

1

I always prefer to use the Cells class (using the R1C1 reference) method, like this:

Cells(rr, col).Formula = ...

If you need a multi-cell range, you do something like this:

Range(Cells(rr, col), Cells(rx, colx)).Formula = ...

But you could also do this:

Range("A5").Formula = ...

Some people will make function to turn the column number into the letter. I think it's tacky, but it would look like this:

Range(MyNumberToLetterFunction(col) & rr).Formula = ...

or a mulit-cell range:

Range("A5:B6").Formula = ...

By the way, it's good practice to qualify your ranges using a sheet reference. It seems to work very well with loops, like you're doing:

Sheet1.Cells(rr, col).Value = ...

Or

Sheets("My Awesome Stuff").Cells(rr, col).Value = ...

edited Aug 10, 2015 at 16:29

answered Aug 10, 2015 at 16:21

LimaNightHawk

7,1434 gold badges45 silver badges61 bronze badges

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

Collectives™ on Stack Overflow

How to refer dynamicaly to an Excel cell with VBA Macro?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related