Excel VBA - loop to fill up ranges with formulas referencing to other ranges

Question

I have 2 worksheets (sht1, sht2) with both having similar size ranges and I'd need to refer with formulas from sht2 to sth1.

Example: on sht2 add formulas to range I10:K11 that link to sht1 range B2:D3 next on sht2 add formulas to range I20:K21 that link to sht1 range B6:D7 and so on as long as there are ranges

There are hundreds of these ranges but on both sheet with separate distances from each other and there is other stuff between the ranges so I can't just populate whole sheets. Distances between ranges on sht1 are constant as well as on sht2, just different from each other.

What would be the best way to populate the range on sht2 with formulas to sht1? Should I just use integer in Cells and then move the integer by known distance between loops?

Use For i = <something> to <something else> STEP <stagger> where the stagger is the constant distance. Apply some rudimentary maths to i for the second stagger. — user4039065

Unknown Unknown · Accepted Answer · 2016-02-05T23:29:22

Contrary to my comment, the maths behind the stagger may be easier to figure out with the Range.Offset property. With .Offset providing the starting point, the Range.Resize property reshapes the target area.

Sub staggerLink()
    Dim r As Long, sht1 As Worksheet

    Set sht1 = Worksheets("sht1")

    With Worksheets("sht2")
        For r = 0 To 99
            .Cells(10, 9).Resize(2, 3).Offset(r * 10, 0).Formula = _
                Chr(61) & sht1.Cells(2, 2).Offset(r * 4, 0).Address(False, False, external:=True)
        Next r
    End With
End Sub

You've provided no scope to your project other than 'hundreds of these ranges' so I simply ran down 100 times. Additionally, you provided no sample or even description of your 'formula' other than it has to 'link to sht1' so that is the formula I provided.

Excel VBA - loop to fill up ranges with formulas referencing to other ranges

1 Answers