Advance formula 3 columns need update

K

Kim B

Board Regular
Joined
Jun 16, 2008
Messages
233
Office Version
  1. 365
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Alright, give this a try:
VBA Code: 
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
 
Upvote 0
Solution
dreid1011 said:
Alright, give this a try:
VBA Code: 
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
Click to expand...
perfect! thanks again
 
Upvote 0
dreid1011 said:
You're welcome.
Click to expand...
Sorry, need one more tweak. Formula is over writing headers. It needs to start on row 6. Rows 1-5 are headers

1710354434785.png
 
Upvote 0
VBA Code: 
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
 
Upvote 0
dreid1011 said:
VBA Code: 
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]
Click to expand...
 

Attachments

  • 1710356829632.png
    1710356829632.png
    68.6 KB · Views: 7
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Advance formula 3 columns
2 3 4
Replies
37
Views
1K
dreid1011
dreid1011
S
  • Question Question
CSV export to remove column
Replies
1
Views
631
StellarRumble59
S
R
  • Question Question
[VBA] - Need to Hide one column and need sum of columns
Replies
0
Views
192
rsolanki
R
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
H
  • Question Question
Advanced Multi VBA - Creating multiple temporary files and attach them to one email on outlook
Replies
2
Views
263
Hayf13
H

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top