Advance formula 3 columns need update

[Kim B]

Hello again. Requesting a revision to this macro. Apologies up front. I was not given the correct information. Can we now include the forecast formula to do the same as the past csv formula, that is to look at store data tab and mimic the x-xx. So:

=SUMIFS('Forecast 1-2'!$D:$D,'Forecast 1-2'!$A:$A,'by Store Data Pull 1-16'!P$4,'Forecast 1-2'!$B:$B,'by Store Data Pull 1-16'!$A6)

Forecast x-x should equal by store data pull 1-xx

Here is existing code

Thanks again


Private Sub EnterFormula2()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim fX1 As String, fX2 As String
Dim shName1 As String, shName2 As String

On Error Resume Next

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each ws In Worksheets
If InStr(ws.Name, "by Store Data Pull") Then
shName1 = "'Paste CSV File Here " & WorksheetFunction.Substitute(ws.Name, "by Store Data Pull ", "") & "'!"
shName2 = "'" & ws.Name & "'!"
For i = 15 To 1256 Step 3
j = i + 1
fX1 = "=SUMIFS(" & shName1 & "C4," & shName1 & "C1," & shName2 & "R4C," & shName1 & "C2," & shName2 & "R[2]C1)"
ws.Range(ws.Cells(4, i), ws.Cells(43, i)).FormulaR1C1 = fX1
fX2 = "=SUMIFS('Forecast 1-9'!C4,'Forecast 1-9'!C1," & shName2 & "R4C,'Forecast 1-9'!C2," & shName2 & "R[2]C1)"
ws.Range(ws.Cells(4, j), ws.Cells(43, j)).FormulaR1C1 = fX2
Next i
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[dreid1011]

I'll post a revision shortly.

 

[Kim B]

I'll post a revision shortly.

thank you.

 

[dreid1011]

Alright, give this a try:

Private Sub EnterFormula2()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim fX1 As String, fX2 As String
Dim shName1 As String, shName2 As String, shName3 As String

On Error Resume Next

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each ws In Worksheets
    If InStr(ws.Name, "by Store Data Pull") Then
        shName1 = "'Paste CSV File Here " & WorksheetFunction.Substitute(ws.Name, "by Store Data Pull ", "") & "'!"
        shName2 = "'" & ws.Name & "'!"
        shName3 = "'Forecast " & WorksheetFunction.Substitute(ws.Name, "by Store Data Pull ", "") & "'!"
        For i = 15 To 1256 Step 3
            j = i + 1
            fX1 = "=SUMIFS(" & shName1 & "C4," & shName1 & "C1," & shName2 & "R4C," & shName1 & "C2," & shName2 & "R[2]C1)"
            ws.Range(ws.Cells(4, i), ws.Cells(43, i)).FormulaR1C1 = fX1
            fX2 = "=SUMIFS(" & shName3 & "C4," & shName3 & "C1," & shName2 & "R4C," & shName3 & "C2," & shName2 & "R[2]C1)"
            ws.Range(ws.Cells(4, j), ws.Cells(43, j)).FormulaR1C1 = fX2
        Next i
    End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[Kim B]

Alright, give this a try:

Private Sub EnterFormula2()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim fX1 As String, fX2 As String
Dim shName1 As String, shName2 As String, shName3 As String

On Error Resume Next

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each ws In Worksheets
    If InStr(ws.Name, "by Store Data Pull") Then
        shName1 = "'Paste CSV File Here " & WorksheetFunction.Substitute(ws.Name, "by Store Data Pull ", "") & "'!"
        shName2 = "'" & ws.Name & "'!"
        shName3 = "'Forecast " & WorksheetFunction.Substitute(ws.Name, "by Store Data Pull ", "") & "'!"
        For i = 15 To 1256 Step 3
            j = i + 1
            fX1 = "=SUMIFS(" & shName1 & "C4," & shName1 & "C1," & shName2 & "R4C," & shName1 & "C2," & shName2 & "R[2]C1)"
            ws.Range(ws.Cells(4, i), ws.Cells(43, i)).FormulaR1C1 = fX1
            fX2 = "=SUMIFS(" & shName3 & "C4," & shName3 & "C1," & shName2 & "R4C," & shName3 & "C2," & shName2 & "R[2]C1)"
            ws.Range(ws.Cells(4, j), ws.Cells(43, j)).FormulaR1C1 = fX2
        Next i
    End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

perfect! thanks again

 

[dreid1011]

You're welcome.

 

[Kim B]

You're welcome.

Sorry, need one more tweak. Formula is over writing headers. It needs to start on row 6. Rows 1-5 are headers

 

[dreid1011]

Private Sub EnterFormula2()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim fX1 As String, fX2 As String
Dim shName1 As String, shName2 As String, shName3 As String

On Error Resume Next

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each ws In Worksheets
    If InStr(ws.Name, "by Store Data Pull") Then
        shName1 = "'Paste CSV File Here " & WorksheetFunction.Substitute(ws.Name, "by Store Data Pull ", "") & "'!"
        shName2 = "'" & ws.Name & "'!"
        shName3 = "'Forecast " & WorksheetFunction.Substitute(ws.Name, "by Store Data Pull ", "") & "'!"
        For i = 15 To 1256 Step 3
            j = i + 1
            fX1 = "=SUMIFS(" & shName1 & "C4," & shName1 & "C1," & shName2 & "R4C," & shName1 & "C2," & shName2 & "R[2]C1)"
            ws.Range(ws.Cells(6, i), ws.Cells(45, i)).FormulaR1C1 = fX1
            fX2 = "=SUMIFS(" & shName3 & "C4," & shName3 & "C1," & shName2 & "R4C," & shName3 & "C2," & shName2 & "R[2]C1)"
            ws.Range(ws.Cells(6, j), ws.Cells(45, j)).FormulaR1C1 = fX2
        Next i
    End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[Kim B]

Private Sub EnterFormula2()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim fX1 As String, fX2 As String
Dim shName1 As String, shName2 As String, shName3 As String

On Error Resume Next

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each ws In Worksheets
    If InStr(ws.Name, "by Store Data Pull") Then
        shName1 = "'Paste CSV File Here " & WorksheetFunction.Substitute(ws.Name, "by Store Data Pull ", "") & "'!"
        shName2 = "'" & ws.Name & "'!"
        shName3 = "'Forecast " & WorksheetFunction.Substitute(ws.Name, "by Store Data Pull ", "") & "'!"
        For i = 15 To 1256 Step 3
            j = i + 1
            fX1 = "=SUMIFS(" & shName1 & "C4," & shName1 & "C1," & shName2 & "R4C," & shName1 & "C2," & shName2 & "R[2]C1)"
            ws.Range(ws.Cells(6, i), ws.Cells(45, i)).FormulaR1C1 = fX1
            fX2 = "=SUMIFS(" & shName3 & "C4," & shName3 & "C1," & shName2 & "R4C," & shName3 & "C2," & shName2 & "R[2]C1)"
            ws.Range(ws.Cells(6, j), ws.Cells(45, j)).FormulaR1C1 = fX2
        Next i
    End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

[/QUOTE]


when I f2 on O6, formula is referencing A8?
[ATTACH type="full"]108304[/ATTACH]