Looping over distinct cell ranges to write formulas in each using VBA

Question

I have a large forecasting spreadsheet I work with and I'm trying to write formulas into distinct ranges of cells (that correspond to months).

Sub prediction()
Dim rng As Range, i As Range, colCount As Integer
Set rng = ThisWorkbook.Worksheets("mySheet").Range("D20:G20, H20:K20, L20:O20, P20:T20, U20:X20, Y20:AB20, AC20:AG20, AH20:AK20, AL20:AP20, AQ20:AT20, AU20:AX20, AY20:BB20")
colCount = 3
For Each i In rng
    i.Formula = "=ROUNDUP(" & Sheets("Forecast").Cells(16, colCount).Address(True, True, xlA1, True) & "/" & Sheets("Forecast").Cells(5, colCount).Address(True, True, xlA1, True) & ",0)"
    colCount = colCount + 1
Next i
End Sub

Those ranges are next to each other in the worksheet, obviously, and what happens is that VBA is writing the formula sequentially. For example, in D20:G20 I want =ROUNDUP(FORECAST!$C$16/FORECAST!$C$5,0), but VBA is writing the next formula in E20 as =ROUNDUP(FORECAST!$D$16/FORECAST!$D$5,0) and so on.

Now, I don't know if this is a problem with the way VBA handles ranges (I'm new to VBA) but the way I solved it was by specifically naming separate ranges and then writing the formula individually.

Set jan = ThisWorkbook.Worksheets("mySheet").Range("D20:G20")
Set feb = ThisWorkbook.Worksheets("mySheet").Range("H20:K20")
jan.Formula = "=ROUNDUP(" & Sheets("Forecast").Cells(16, 3).Address(True, True, xlA1, True) & "/" & Sheets("Forecast").Cells(5, 3).Address(True, True, xlA1, True) & ",0)"
feb.Formula = "=ROUNDUP(" & Sheets("Forecast").Cells(16, 4).Address(True, True, xlA1, True) & "/" & Sheets("Forecast").Cells(5, 4).Address(True, True, xlA1, True) & ",0)"

Etc etc. This doesn't seem elegant, let alone efficient.

Question: is the problem with how I'm naming the range, or with how I'm constructing my loop?

Thanks.

You need to use the .Areas property, so using your original code, your For Each line becomes For Each i In rng.Areas and then it will populate the formula as desired. — tigeravatar
Without the .Areas it assumes you want .Cells so then it loops through each cell individually, instead of each defined cell grouping (which is termed an Area in VBA) — tigeravatar

mongoose36 mongoose36 · Accepted Answer · 2016-06-28T02:53:21

Try this... It utilizes an array to convert the range groups ("D20:G20" etc...) into array elements that can be evaluated individually when you loop through the array with For i = lbound(array) to ubound(array)...lbound stands for lower bound and ubound stands for upper bound). then each array element is converted to a range by Set rng = ThisWorkbook.Worksheets("mySheet").Range(RngArr(i)). Finally you iterate through each cell in the range by For Each r In rng. This allows the column integer to only increment fore each array element.

 Sub prediction()
    Dim RngStr As String, i As Integer, colCount As Integer
    Dim RngArr
    Dim rng As Range, r As Range
    RngArr = "D20:G20, H20:K20, L20:O20, P20:T20, U20:X20, Y20:AB20, AC20:AG20, AH20:AK20, AL20:AP20, AQ20:AT20, AU20:AX20, AY20:BB20"
    RngArr = Split(RngArr, ",")
    colCount = 3
    For i = LBound(RngArr) To UBound(RngArr)
        Set rng = ThisWorkbook.Worksheets("mySheet").Range(RngArr(i))
        For Each r In rng
            r.Formula = "=ROUNDUP(" & Sheets("Forecast").Cells(16, colCount).Address(True, True, xlA1, True) & "/" & Sheets("Forecast").Cells(5, colCount).Address(True, True, xlA1, True) & ",0)"
        Next r
        colCount = colCount + 1
    Next i
    End Sub

Looping over distinct cell ranges to write formulas in each using VBA

1 Answers