VBA - Intersect at Column values 'Total' and 'Oranges' . Sum offset 2 columns to left.

[MikeL]

Hello,
I want to add an offset formula to sum two columns to the left of the selected cell.

The selected cell will be the intersection of the column field 'Total' and the row value 'Oranges'. These values create an intersection 3x in the attached worksheet sample.

Thanks in advance

CATEGORY	2018Q1-Q2	2018Q3-Q4	TOTAL	2019Q1-Q2	2019Q3-Q4	TOTAL	2020Q1-Q2	2020Q3-Q4	TOTAL
BERRIES	15	1		20	3		18	0
ORANGES	10	12	22	3	10	13	11	10	21
APPLES	3	4		8	2		6	3
ORANGES	5	10	15	4	10	14	9	20	29
PEARS	3	4		8	2		6	3
ORANGES	10	20	30	2	10	12	7	5	12
BERRIES	3	4		8	2		6	3

 

[Peter_SSs]

What columns and rows can we see? When using XL2BB if you use Mini Sheet instead of Table Only, we would be able to see that information.

You only want to insert a formula for oranges and not the other values?

BTW, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[MikeL]

Hi, Yes only for rows with 'Oranges'. XL 2013, Windows updated

VBA_SAMPLE.xlsx
	A	B	C	D	E	F	G	H	I	J
1	CATEGORY	2018Q1-Q2	2018Q3-Q4	TOTAL	2019Q1-Q2	2019Q3-Q4	TOTAL	2020Q1-Q2	2020Q3-Q4	TOTAL
2	BERRIES	15	1		20	3		18	0
3	ORANGES	10	12	22	3	10	13	11	10	21
4	APPLES	3	4		8	2		6	3
5	ORANGES	5	10	15	4	10	14	9	20	29
6	PEARS	3	4		8	2		6	3
7	ORANGES	10	20	30	2	10	12	7	5	12
8	BERRIES	3	4		8	2		6	3
AFTER

Cell Formulas
Range	Formula
D3,J7,G7,D7,J5,G5,D5,J3,G3	D3	=SUM(B3:C3)

 

[Peter_SSs]

Thanks for updating your profile and for the new mini-sheet.

See if this does what you want. Test with a copy of your workbook.

Sub Total_Oranges()
  Application.ScreenUpdating = False
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:="ORANGES"
    If .SpecialCells(xlVisible).Count > 1 Then Intersect(.Offset(1).Resize(.Rows.Count - 1).EntireRow, Range("D:D,G:G,J:J")).FormulaR1C1 = "=RC[-2]+RC[-1]"
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub

 

[MikeL]

Range("D:D,G:G,J:J"))

Thanks Peter. The Range where 'Total' is variable. It could be different columns and can range from 1 column to 6 columns in the Workbook. Any update for this piece of code?

 

[Peter_SSs]

Assuming that it is still adding the 2 columns to the left as you originally described, and 'CATEGORY' is still in cell A1, try

Sub Total_Oranges_v2()
  Dim rCol As Range
  Dim DataRows As Long
  
  Application.ScreenUpdating = False
  With Range("A1").CurrentRegion
    DataRows = .Rows.Count - 1
    .AutoFilter Field:=1, Criteria1:="ORANGES"
    If .Columns(1).SpecialCells(xlVisible).Count > 1 Then
      For Each rCol In .Columns
        If rCol.Cells(1).Value = "TOTAL" Then rCol.Offset(1).Resize(DataRows).SpecialCells(xlVisible).FormulaR1C1 = "=RC[-2]+RC[-1]"
      Next rCol
    End If
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub