CODEWITHSUNDEEP
vbaexcel
Benjamin Scott
Benjamin Scott

Reputation: 85

Adding a formula to noncontinuous cells from multiple columns in Excel VBA

I have a spreadsheet of forecasting data, and I need to continually update the forecast cells. Doing this manually takes hours (it's several hundred cells). I'm trying to write a macro that writes a formula in each cell and I'm stuck on the syntax. This is my prototype.

Question: How to correctly loop through the cells from the Forecast tab?

Sub forecastBuild()
Dim rng As Range, i As Range, colCount As Integer, col As Integer
colCount = 14
Set rng = Range("D6, H6, L6, P6, U6, Y6, AC6, AH6, AL6, AQ6, AU6, AY6")

For Each i In rng
    For col = 3 To colCount
        i.Formula = "=Forecast!$" & columns(, col) & "$7"
    Next col
Next i
End Sub

rng is my output range (where the formula is to be written, for now) and I'm taking the data from Forecast!C7:Forecast!N7. I do not believe the columns(, col) syntax is correct, and VBA is not converting the integer to the string. Thanks.

Upvotes: 1

Views: 163

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

I think this is what you want:

Sub forecastBuild()
Dim rng As Range, i As Range, colCount As Integer, col As Integer
Dim ws As Worksheet
Set ws = ActiveSheet

Set rng = ws.Range("D6, H6, L6, P6, U6, Y6, AC6, AH6, AL6, AQ6, AU6, AY6")
colCount = 3
For Each i In rng
    i.Formula = "=" & Sheets("Forecast").Cells(7, colCount).Address(0, 0, xlA1, True)
    colCount = colCount + 1
Next i
End Sub

Upvotes: 2

Ralph
Ralph

Reputation: 9444

I am not quite sure if I understood correctly. But maybe you're looking for something along the line of this:

Option Explicit

Sub forecastBuild()

Dim rng As Range, i As Range, colCount As Integer

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("D6, H6, L6, P6, U6, Y6, AC6, AH6, AL6, AQ6, AU6, AY6")

For Each i In rng
    i.Formula = "=Forecast!" & Cells(7, 3 + colCount).Address
    colCount = colCount + 1
Next i

End Sub

Upvotes: 1

Related Questions