none
formula based on column heading and row content RRS feed

  • 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
    Sunday, May 13, 2012 11:57 AM

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
    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 Vbaer 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 Vbaer 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
            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
    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