Reference single cells in a column to the left of merged cells

[wallen1605]

Good morning

Please could someone help me with the VBA code to reference individual cells to the left of a merged cell? I know the issues around using merged cells in excel, however i need to keep them in this case.

Basically, i have code that checks column A for the same reference and then merges column H cells (e.g Cells A2, A3, A4 have the same cell value, cells H2, H3, H4 are merged and so on down the rows. Cells B2, B3, B4 each have a number and i need to add these together and show the total in the merged cell of A2, A3, A4, so i need to reference the cells from the merged cell. Obviously i can use an offset of mergearea(1).address but this only retrieves the value in B2. Please could someone help on the code line i need to reference all cells adjacent to the merge cells.

Thanks in advance.

 

[arthurbr]

Why not use " Center across selection" instead of merging. You can than easily reference the cell you need

 

[wallen1605]

Why not use " Center across selection" instead of merging. You can than easily reference the cell you need

Yes, i thought of that as well, unfortunately my boss does not like that appearance. Is there any way to reference each cell from a merged cell (note, the amount of cells in a merged cell can differ)?

 

[Peter_SSs]

Cells A2, A3, A4 have the same cell value
...show the total in the merged cell of A2, A3, A4,

That is not clear to me. Firstly you say A2, A3 and A4 have the same value but then you want to put a value in that merged area?

Why not use " Center across selection" instead of merging.

Yes, i thought of that as well,

Center Selection does have a vertical equivalent (A2, A3 & A4 being vertical) so I'm not sure what you actually envisage for that?


Any chance we could have a small set of dummy data and expected results with XL2BB and any further explanation in relation to that sample?

 

[wallen1605]

That is not clear to me. Firstly you say A2, A3 and A4 have the same value but then you want to put a value in that merged area?




Center Selection does have a vertical equivalent (A2, A3 & A4 being vertical) so I'm not sure what you actually envisage for that?


Any chance we could have a small set of dummy data and expected results with XL2BB and any further explanation in relation to that sample?

No problem, please see below:

AWTestSystem.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	INTERNAL REF	ORDER DATE	BOOKED OUT	ITEM SENT	QTY	CUSTOMER NAME	ADDRESS	TOTAL ITEMS	PICKING COST	POSTAGE COST	TOTAL COST	BOX QTY
2	SR_2019_1040_160819_093005	19/07/19	22/07/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
3	SR_2019_1040_160819_093005	19/07/19	22/07/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
4	SR_2019_1040_160819_093005	19/07/19	22/07/19	ITEM NAME	3	NAME	CUSTOMER ADDRESS
5	SR_2019_1040_160819_093005	19/07/19	22/07/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
6
7	SR_2019_1040_170819_093005	29/07/19	29/07/19	ITEM NAME	10	NAME	CUSTOMER ADDRESS
8
9	SR_2019_1040_180819_093005	06/08/19	06/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
10	SR_2019_1040_180819_093005	06/08/19	06/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
11	SR_2019_1040_180819_093005	06/08/19	06/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
12
13	SR_2019_1040_190819_093005	15/08/19	15/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
14	SR_2019_1040_190819_093005	15/08/19	15/08/19	ITEM NAME	5	NAME	CUSTOMER ADDRESS
15	SR_2019_1040_190819_093005	15/08/19	15/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
16	SR_2019_1040_190819_093005	15/08/19	15/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
17
18	SR_2019_1003_200819_102959	22/08/19	22/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
19	SR_2019_1003_200819_102959	22/08/19	22/08/19	ITEM NAME	2	NAME	CUSTOMER ADDRESS
20	SR_2019_1003_200819_102959	22/08/19	22/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
21
22	SR_2019_1004_310819_140905	31/08/19	02/09/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
23	SR_2019_1004_310819_140905	31/08/19	02/09/19	ITEM NAME	8	NAME	CUSTOMER ADDRESS
24	SR_2019_1004_310819_140905	31/08/19	02/09/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
25	SR_2019_1004_310819_140905	31/08/19	02/09/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
Sheet2

 

[wallen1605]

No problem, please see below:

AWTestSystem.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	INTERNAL REF	ORDER DATE	BOOKED OUT	ITEM SENT	QTY	CUSTOMER NAME	ADDRESS	TOTAL ITEMS	PICKING COST	POSTAGE COST	TOTAL COST	BOX QTY
2	SR_2019_1040_160819_093005	19/07/19	22/07/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
3	SR_2019_1040_160819_093005	19/07/19	22/07/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
4	SR_2019_1040_160819_093005	19/07/19	22/07/19	ITEM NAME	3	NAME	CUSTOMER ADDRESS
5	SR_2019_1040_160819_093005	19/07/19	22/07/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
6
7	SR_2019_1040_170819_093005	29/07/19	29/07/19	ITEM NAME	10	NAME	CUSTOMER ADDRESS
8
9	SR_2019_1040_180819_093005	06/08/19	06/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
10	SR_2019_1040_180819_093005	06/08/19	06/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
11	SR_2019_1040_180819_093005	06/08/19	06/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
12
13	SR_2019_1040_190819_093005	15/08/19	15/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
14	SR_2019_1040_190819_093005	15/08/19	15/08/19	ITEM NAME	5	NAME	CUSTOMER ADDRESS
15	SR_2019_1040_190819_093005	15/08/19	15/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
16	SR_2019_1040_190819_093005	15/08/19	15/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
17
18	SR_2019_1003_200819_102959	22/08/19	22/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
19	SR_2019_1003_200819_102959	22/08/19	22/08/19	ITEM NAME	2	NAME	CUSTOMER ADDRESS
20	SR_2019_1003_200819_102959	22/08/19	22/08/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
21
22	SR_2019_1004_310819_140905	31/08/19	02/09/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
23	SR_2019_1004_310819_140905	31/08/19	02/09/19	ITEM NAME	8	NAME	CUSTOMER ADDRESS
24	SR_2019_1004_310819_140905	31/08/19	02/09/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
25	SR_2019_1004_310819_140905	31/08/19	02/09/19	ITEM NAME	1	NAME	CUSTOMER ADDRESS
Sheet2

So, i have code that looks through the entire sheet and for each row with matching Internal Ref`s, it will merge the rows in columns H, I, J, K and L. The code then also adds the prices, based on the POSTAGE cost previously entered by a user. I still need each row if data to remain, but to sum the Qty cells (where the Internal Ref matches) and put the total in the row cell of column H.

 

[Peter_SSs]

Thanks, that is much easier to understand with the sample.

Could you use something like this?

Sub Tot_Qty()
  Dim rA As Range
  
  For Each rA In Columns("E").SpecialCells(xlConstants, xlNumbers).Areas
    rA.Cells(1, 4).Value = Application.Sum(rA)
  Next rA
End Sub

 

[wallen1605]

Thanks, that is much easier to understand with the sample.

Could you use something like this?

Sub Tot_Qty()
  Dim rA As Range
 
  For Each rA In Columns("E").SpecialCells(xlConstants, xlNumbers).Areas
    rA.Cells(1, 4).Value = Application.Sum(rA)
  Next rA
End Sub

Thank you very much, that worked brilliant. Can i ask please, if all rows had data instead of grouped with a blank row inbetween, what would i change the code to? (I assume the .Areas is where the change needs to be?).

Thanks in advance.

 

[Peter_SSs]

if all rows had data instead of grouped with a blank row inbetween, what would i change the code to?

Try

Sub Tot_Qty_v2()
  Dim rw As Long
  
  rw = 2
  Do Until rw > Range("A" & Rows.Count).End(xlUp).Row
    With Cells(rw, "H").MergeArea
      .Value = Application.Sum(.Offset(, -3).Resize(.Rows.Count))
      rw = rw + .Rows.Count
    End With
  Loop
End Sub

 

[wallen1605]

Try

Sub Tot_Qty_v2()
  Dim rw As Long
 
  rw = 2
  Do Until rw > Range("A" & Rows.Count).End(xlUp).Row
    With Cells(rw, "H").MergeArea
      .Value = Application.Sum(.Offset(, -3).Resize(.Rows.Count))
      rw = rw + .Rows.Count
    End With
  Loop
End Sub

Thanks Peter, that works great too. Thank you very much for your help.