Excel changing cell references when columns inserted - VBA needed

[jeffdolton]

Hello, I don't think the answer is as straightforward as the question implies! I have a workbook with four worksheets. In Sheet1 I copy in data from an EPOS system and then I run code provided by members of this Forum that firstly splits a string that contains different products purchased, then code applies a lookup to obtain price and category and finally code inserts two columns and totals the price for each product.

On Sheet 2, I have attempted to sum the total price of each category in Sheet 1 - I tried to obtain a formula to sum every 5th column to the nth, but without success.

The problem is that when the third code is run the cell references in the sumifs formula on Sheet 2 changes even though I have accounted for the insertion of columns and some cell references are locked by using $$. I'm very confused.

Hope you can help - I think this probably needs code to sum the categories.

 

[maabadi]

This is Very Easy. I make easier for you. Only Input First cell of each range & Criteria range,
Example Select S2 to Formula Select S2:CC100000
Select Q2 to Formula Select Q2:Q100000
Select J2 to Formula Select J2:J100000
For Criteria you can Input names
Formula Example:---------------------SumRange-------------Criteriarange1---Criteria1------CriteriaRange2 -----Criteria2 ---Criteria3

=SumIfsEveryNColumns('Receipts Output'!$S$2,'Receipts Output'!$Q$2,"kitchen",'Receipts Output'!$J$2,"Contactless","Chip")

Function SumIfsEveryNColumns(FirstCellSumRng As Range, CrRng1 As Range, Cr1 As Variant, CrRng2 As Range, Cr2 As Variant, Cr3 As Variant, Optional CrRng4 As Range, Optional Cr4 As Variant) As Double
Dim i As Long, j As Long, Lr1 As Long, Lc As Long, ws1 As Worksheet, ws2 As Worksheet
', SumRng As Range, CrRng1 As Range, Cr1 As Variant, CrRng2 As Range, Cr2 As Variant, Cr3 As Variant, CrRng4 As Range, Cr4 As Range
Dim P1 As Double, P2 As Double, P As Double, SumRng As Range, CrR1 As Range, CrR2 As Range
Set ws1 = Sheets("Receipts Output")
Lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Lc = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
Set CrR1 = Range(ws1.Cells(2, CrRng1.Column), ws1.Cells(Lr1, CrRng1.Column))
Set CrR2 = Range(ws1.Cells(2, CrRng2.Column), ws1.Cells(Lr1, CrRng2.Column))
'Debug.Print CrRng1.Address
'Set Cr1 = ws1.Cells(4, 17)
'Set CrRng2 = Range(ws1.Cells(2, 10), ws1.Cells(Lr1, 10))
'Debug.Print CrRng2.Address
'Set Cr2 = ws1.Cells(4, 10)
'Set Cr3 = ws1.Cells(5, 10)
For j = FirstCellSumRng.Column To Lc Step 5
Set SumRng = Range(ws1.Cells(FirstCellSumRng.Row, j), ws1.Cells(Lr1, j))
Debug.Print SumRng.Address
P1 = Application.WorksheetFunction.SumIfs(SumRng, CrR1, Cr1, CrR2, Cr2)
P2 = Application.WorksheetFunction.SumIfs(SumRng, CrR1, Cr1, CrR2, Cr3)
P = P1 + P2 + P

', CrRng3, Cr3, CrRng4, Cr4, CrRng5, Cr5)
Next j
Debug.Print P
SumIfsEveryNColumns = P
End Function

 

[maabadi]

IF again have problems only input Criteria with this function:
Example:

=SumIfsEveryNColumns("kitchen","chip","Contactless")

Function SumIfsEveryNColumns(Cr1 As Variant, Cr2 As Variant, Cr3 As Variant) As Double
Dim i As Long, j As Long, Lr1 As Long, Lc As Long, ws1 As Worksheet, ws2 As Worksheet
Dim P1 As Double, P2 As Double, P As Double, SumRng As Range, CrR1 As Range, CrR2 As Range
Set ws1 = Sheets("Receipts Output")
Lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Lc = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
Set CrR1 = Range(ws1.Cells(2, 17), ws1.Cells(Lr1, 17))
Set CrR2 = Range(ws1.Cells(2, 10), ws1.Cells(Lr1, 10))

For j = 19 To Lc Step 5
Set SumRng = Range(ws1.Cells(2, j), ws1.Cells(Lr1, j))
Debug.Print SumRng.Address
P1 = Application.WorksheetFunction.SumIfs(SumRng, CrR1, Cr1, CrR2, Cr2)
P2 = Application.WorksheetFunction.SumIfs(SumRng, CrR1, Cr1, CrR2, Cr3)
P = P1 + P2 + P
Next j
Debug.Print P
SumIfsEveryNColumns = P
End Function

 

[jeffdolton]

IF again have problems only input Criteria with this function:
Example:

=SumIfsEveryNColumns("kitchen","chip","Contactless")

Function SumIfsEveryNColumns(Cr1 As Variant, Cr2 As Variant, Cr3 As Variant) As Double
Dim i As Long, j As Long, Lr1 As Long, Lc As Long, ws1 As Worksheet, ws2 As Worksheet
Dim P1 As Double, P2 As Double, P As Double, SumRng As Range, CrR1 As Range, CrR2 As Range
Set ws1 = Sheets("Receipts Output")
Lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Lc = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
Set CrR1 = Range(ws1.Cells(2, 17), ws1.Cells(Lr1, 17))
Set CrR2 = Range(ws1.Cells(2, 10), ws1.Cells(Lr1, 10))

For j = 19 To Lc Step 5
Set SumRng = Range(ws1.Cells(2, j), ws1.Cells(Lr1, j))
Debug.Print SumRng.Address
P1 = Application.WorksheetFunction.SumIfs(SumRng, CrR1, Cr1, CrR2, Cr2)
P2 = Application.WorksheetFunction.SumIfs(SumRng, CrR1, Cr1, CrR2, Cr3)
P = P1 + P2 + P
Next j
Debug.Print P
SumIfsEveryNColumns = P
End Function

Hi again. I'm about to give up unfortunately. You say its easy but I can't see it. I've added ranges and criteria as below but it doesn't work, I just get #NAME. Can you please add the ranges and criteria into the code so I can see how it works. Thanks.
Function SumIfsEveryNColumns(FirstCellSumRng As Range, CrRng1 As Range, Cr1 As Variant, CrRng2 As Range, Cr2 As Variant, Cr3 As Variant, Optional CrRng4 As Range, Optional Cr4 As Variant) As Double
Dim i As Long, j As Long, Lr1 As Long, Lc As Long, ws1 As Worksheet, ws2 As Worksheet
Dim P1 As Double, P2 As Double, P As Double, SumRng As Range
Set ws1 = Sheets("Receipts Output")
Lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Lc = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
For j = FirstCellSumRng.Column To Lc Step 5
Set SumRng = Range(ws1.Cells(FirstCellSumRng.Row, j), ws1.Cells(Lr1, j))
Debug.Print SumRng.Address
P1 = Application.WorksheetFunction.SumIfs(S2, q2, Kitchen)
P2 = Application.WorksheetFunction.SumIfs(S2, j2, Contactless, j2, Chip)
P = P1 + P2 + P
Next j
Debug.Print P
SumIfsEveryNColumns = P
End Function

 

[maabadi]

Sorry. I give up.
1. Please Delete complete previous codes for this Work
2. Please ADD Post 21 VBA Code and Test it exactly with my method at Post 21 (e.g. Example at Post 21) to input Data With Arrangement I told.

Example Select S2 to Formula Select S2:CC100000
Select Q2 to Formula Select Q2:Q100000
Select J2 to Formula Select J2:J100000
For Criteria you can Input names
Formula Example:---------------------SumRange-------------Criteriarange1---Criteria1------CriteriaRange2 -----Criteria2 ---Criteria3

=SumIfsEveryNColumns('Receipts Output'!$S$2,'Receipts Output'!$Q$2,"kitchen",'Receipts Output'!$J$2,"Contactless","Chip")

OR

3. Please ADD Post 22 VBA Code and Test it exactly with my method at Post 22 (e.g. Example at Post 22) to input Data With Arrangement I told. (without adding Ranges only add Creiteria: "kitchen","Contactless","Chip"

only input Criteria with this function:
Example:

=SumIfsEveryNColumns("kitchen","chip","Contactless")

 

[jeffdolton]

Sorry. I give up.
1. Please Delete complete previous codes for this Work
2. Please ADD Post 21 VBA Code and Test it exactly with my method at Post 21 (e.g. Example at Post 21) to input Data With Arrangement I told.

=SumIfsEveryNColumns('Receipts Output'!$S$2,'Receipts Output'!$Q$2,"kitchen",'Receipts Output'!$J$2,"Contactless","Chip")

OR

3. Please ADD Post 22 VBA Code and Test it exactly with my method at Post 22 (e.g. Example at Post 22) to input Data With Arrangement I told. (without adding Ranges only add Creiteria: "kitchen","Contactless","Chip"

=SumIfsEveryNColumns("kitchen","chip","Contactless")

HOORAY! I understand now, have entered the criteria into the function and it WORKS! Have tested it multiple times and all okay.

Thank you so much for your time.

 

[maabadi]

You're Welcome & Glad you Understand Finally.