CODEWITHSUNDEEP
vbaexcel
Benjamin Scott
Benjamin Scott

Reputation: 85

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

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.

Upvotes: 2

Views: 161

Answers (1)

mongoose36
mongoose36

Reputation: 799

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

Upvotes: 2

Related Questions