Answered by:
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 Vbaer Sunday, May 13, 2012 2:16 PM
Answers

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 MsgBox "Summary not found in first row!", vbExclamation 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 Vbaer Sunday, May 13, 2012 3:56 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 Vbaer Sunday, May 13, 2012 2:35 PM
All replies



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 Vbaer Sunday, May 13, 2012 2:35 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.

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 MsgBox "Summary not found in first row!", vbExclamation 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 Vbaer Sunday, May 13, 2012 3:56 PM
