Advance formula 3 columns

[Kim B]

Hi all. I need help with adjusting every three columns for cell ref below. Everything else in the formula stays the same


Starting in column O, the formula is copied into R and then U and so on.
=SUMIFS('Paste CSV File Here 1-9'!$D:$D,'Paste CSV File Here 1-9'!$A:$A,'by Store Data Pull 1-9'!O$4,'Paste CSV File Here 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)

needs to read when copied to column R

=SUMIFS('Paste CSV File Here 1-9'!$D:$D,'Paste CSV File Here 1-9'!$A:$A,'by Store Data Pull 1-9'!R$4,'Paste CSV File Here 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)


Starting in column P, the formula is copied into S and so on.
=SUMIFS('Forecast 1-9'!$D:$D,'Forecast 1-9'!$A:$A,'by Store Data Pull 1-9'!P$4,'Forecast 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)

needs to read when copied to column S

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

Thanks for any help

 

[Kim B]

Highlight row

this one incase you can't see it

ws.Range(ws.Cells(4, i), ws.Cells(43, i)).Formula2 = frm1

 

[dreid1011]

this one incase you can't see it

ws.Range(ws.Cells(4, i), ws.Cells(43, i)).Formula2 = frm1

I saw it. I was asking after the specific error message, but I am getting the same thing on my side. I tested all of it except for the actual formulas, so I am trying to work through those at the moment.

 

[dreid1011]

Okay, see if this does any better:

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

Application.ScreenUpdating = False
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.ScreenUpdating = True

End Sub

 

[Kim B]

Okay, see if this does any better:

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

Application.ScreenUpdating = False
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.ScreenUpdating = True

End Sub

Taking an abnormal amount of time to run. and when I hit escape, fx2 line is highlighted. The forecast tab doesn't need updating. It is just the by store date pull tabs that need the paste csv formulas updated to match the store data pull tab x-xx. should formula readh "by store data pull instead of forecast1-9? or remove that line althogether?

 

[dreid1011]

Taking an abnormal amount of time to run.

How many sheets are in the workbook? I was only testing on 5, but it usually wasn't too long.

and when I hit escape, fx2 line is highlighted. The forecast tab doesn't need updating. It is just the by store date pull tabs that need the paste csv formulas updated to match the store data pull tab x-xx.

I don't know why it would be highlighting that line. Further testing may be in order.

should formula readh "by store data pull instead of forecast1-9? or remove that line althogether?

Should it? I just copied the exact formula from your original post and put it where you said it should go.

 

[Kim B]

How many sheets are in the workbook? I was only testing on 5, but it usually wasn't too long.


I don't know why it would be highlighting that line. Further testing may be in order.


Should it? I just copied the exact formula from your original post and put it where you said it should go.

 

[Kim B]

The original was to update the cell reference to skip every two rows and then they changed it on me to only needing the paste formula to be upated to match what the store data pull tab says.

So all we need now is to have the paste csv formulas to agree to the red by store data pull x-xx, which has 31 tabs for January starting in column O for all store data pull tabs
=SUMIFS('Paste CSV File Here 1-2'!$D:$D,'Paste CSV File Here 1-2'!$A:$A,'by Store Data Pull 1-12'!O$4,'Paste CSV File Here 1-2'!$B:$B,'by Store Data Pull 1-12'!$A6). Each day of the month has 3 tabs. The forecast and paste csv tabs are filled with data. The by store data pull has the paste csv reference formula that needs to match the tab date.

 

[dreid1011]

Okay, see if this helps with speed:

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

 

[Kim B]

Okay, see if this helps with speed:

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

I am getting an error on 1st line, Private sub enterformula2 (). And then when I hit debug, last line True turns blue

 

[dreid1011]

What is the specific error message?