Excel changing cell references when columns inserted - VBA needed

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Excel Formula:
=SumIfsEveryNColumns('Receipts Output'!$S$2,'Receipts Output'!$Q$2,"kitchen",'Receipts Output'!$J$2,"Contactless","Chip")

VBA Code:
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
IF again have problems only input Criteria with this function:
Example:
Excel Formula:
=SumIfsEveryNColumns("kitchen","chip","Contactless")
VBA Code:
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

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
IF again have problems only input Criteria with this function:
Example:
Excel Formula:
=SumIfsEveryNColumns("kitchen","chip","Contactless")
VBA Code:
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Excel Formula:
=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:
Excel Formula:
=SumIfsEveryNColumns("kitchen","chip","Contactless")
 
Solution

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
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.


Excel Formula:
=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"

Excel Formula:
=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

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Glad you Understand Finally.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,715
Members
414,401
Latest member
grenona2020

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
Top