# formula based on column heading and row content • ### Question

• heading1 heading2 Particulars quarter1 quarter2 quarter3 Summary AllquarterSales Percentage c d a AllquarterSales Percentage d

Suppose if heading1 to heading7 are there in the first row of the worksheet (A1 to G1)

I wanted to add some formula in the cells below Summary (i.e. in the column G)

Rules:

1. If there is "AllquarterSales" in C2, then i want to add D2+E2+F2 in G2 (i.e. in cell G2: =Sum(D2,E2,F2))

Similarly in cell C7, again there is AllquarterSales, there formula should be D7+E7+F7 in cell G7

2. If there is "Percentage" in C3, then i want the formula to multiply  D3*E3*F3 in G3 (i.e. in Cell G3:=D3*E3*F3)

Similarly in cell C8, again there is percentage, there formula should be D8+E8+F8 in cell G8

3. This should loop till the end of that column (Particulars), because "AllquarterSales", "Percentage" may be there many times in the column "Particulars".

Can you help?

• Edited by Sunday, May 13, 2012 2:16 PM
Sunday, May 13, 2012 11:57 AM

• Try this version:

```Sub CreateFormulas()
Dim r As Long
Dim m As Long
Dim c As Long
On Error Resume Next
c = Range("1:1").Find(What:="Summary", LookAt:=xlWhole, _
MatchCase:=False).Column
On Error GoTo 0
If c = 0 Then
Exit Sub
End If
m = Cells(Rows.Count, c - 4).End(xlUp).Row
For r = 2 To m
Select Case UCase(Cells(r, c - 4).Value)
Case "ALLQUARTERSALES"
Cells(r, c).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
Case "PERCENTAGE"
Cells(r, c).FormulaR1C1 = "=PRODUCT(RC[-3]:RC[-1])"
Case Else
Cells(r, c).ClearContents
End Select
Next r
End Sub```

Regards, Hans Vogelaar

• Marked as answer by Sunday, May 13, 2012 3:56 PM
Sunday, May 13, 2012 3:28 PM
• You could do this without VBA code:

In G2, enter the formula

=IF(C2="Allquartersales",SUM(D2:F2),IF(C2="Percentage",PRODUCT(D2:F2),""))

Fill down to the end of the data.

If you prefer a macro:

```Sub CreateFormulas():
Dim r As Long
Dim m As Long
m = Range("C" & Rows.Count).End(xlUp).Row
For r = 2 To m
Select Case UCase(Range("C" & r).Value)
Case "ALLQUARTERSALES"
Range("G" & r).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
Case "PERCENTAGE"
Range("G" & r).FormulaR1C1 = "=PRODUCT(RC[-3]:RC[-1])"
Case Else
Range("G" & r).ClearContents
End Select
Next r
End Sub```

Regards, Hans Vogelaar

• Marked as answer by Sunday, May 13, 2012 2:35 PM
Sunday, May 13, 2012 2:30 PM

### All replies

• I don't understand - the first row is not A1 to A7 but A1 to G1.

And can you explain the "rules" that determine what kind of formula you want?

Regards, Hans Vogelaar

Sunday, May 13, 2012 1:15 PM
• Yes Hans, i was wrong last time, now i have edited it,

Hope i will be undrestandable this time.

Sunday, May 13, 2012 2:14 PM
• You could do this without VBA code:

In G2, enter the formula

=IF(C2="Allquartersales",SUM(D2:F2),IF(C2="Percentage",PRODUCT(D2:F2),""))

Fill down to the end of the data.

If you prefer a macro:

```Sub CreateFormulas():
Dim r As Long
Dim m As Long
m = Range("C" & Rows.Count).End(xlUp).Row
For r = 2 To m
Select Case UCase(Range("C" & r).Value)
Case "ALLQUARTERSALES"
Range("G" & r).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
Case "PERCENTAGE"
Range("G" & r).FormulaR1C1 = "=PRODUCT(RC[-3]:RC[-1])"
Case Else
Range("G" & r).ClearContents
End Select
Next r
End Sub```

Regards, Hans Vogelaar

• Marked as answer by Sunday, May 13, 2012 2:35 PM
Sunday, May 13, 2012 2:30 PM
• Thank you so much Hans Vogelaar.!

That macro works just fine.

I wanted to ask one small suggestion.  I mean If shift entire things from A1 to A7 to some other cells it sholud work.

Range("G" & r) is fine. But can we make excel to select the column (like G) based on whether that column is summary column.

If in S column there is summary instead of G column can we make excel to select range("S" & r) in the code automatically?

But all other cell columns will be relative to the summary column, Means in Particulars will always be on the left 4th column. Similarly other columns. So other part in code is perfect.

Only column should be selected based where Summary is there.

Sunday, May 13, 2012 2:48 PM
• Try this version:

```Sub CreateFormulas()
Dim r As Long
Dim m As Long
Dim c As Long
On Error Resume Next
c = Range("1:1").Find(What:="Summary", LookAt:=xlWhole, _
MatchCase:=False).Column
On Error GoTo 0
If c = 0 Then
Exit Sub
End If
m = Cells(Rows.Count, c - 4).End(xlUp).Row
For r = 2 To m
Select Case UCase(Cells(r, c - 4).Value)
Case "ALLQUARTERSALES"
Cells(r, c).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
Case "PERCENTAGE"
Cells(r, c).FormulaR1C1 = "=PRODUCT(RC[-3]:RC[-1])"
Case Else
Cells(r, c).ClearContents
End Select
Next r
End Sub```

Regards, Hans Vogelaar

• Marked as answer by Sunday, May 13, 2012 3:56 PM
Sunday, May 13, 2012 3:28 PM
• Sub ThankYouSoMuchHansVogelaar()

MsgBox "Your Help is very much appreciated"

You have given your valuable time t solve it.

You are great Hans!

Sunday, May 13, 2012 4:16 PM