asked on

Macros and Formulas

I have a formula that reads: =SUM(INDEX(1:10,,MATCH("column ex",1:1,0))) because I want to be able to sum a column based on the column name no matter where it is on the sheet.

However, I want to use a macro that would allow me to insert a sheet, make column A1 = " Total Cost" and make column B1 = "Quantity", then insert this formula into A2.

Please help!

Wayne Taylor (webtubbs)

Performing those operations manually while recording a macro generated this...

Sub Macro1()

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Total Cost"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Quantity"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUM(INDEX(R[-1]:R[8],,MATCH(""column ex"",R[-1],0)))"

End Sub

Open in new window

That can then be shortened down to this...

Sub Macro1()

    Range("A1").FormulaR1C1 = "Total Cost"
    Range("B1").FormulaR1C1 = "Quantity"
    Range("A2").FormulaR1C1 = _
        "=SUM(INDEX(R[-1]:R[8],,MATCH(""column ex"",R[-1],0)))"

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

You should really check if the sheet exists before attempting to create it

Option Explicit

Sub AddSheet()
    Const NewSht As String = "Summary"
    If WksExists(NewSht) Then
        Select Case MsgBox("A sheet named " & NewSht & _
                         " already exists. Do you want to delete it?", vbYesNo Or vbExclamation Or _
                                     vbDefaultButton1, "Sheet exists")

        Case vbYes
            Application.DisplayAlerts = False
            Worksheets(NewSht).Delete
            Application.DisplayAlerts = True
        Case vbNo
        Exit Sub
            End Select
        End If
        
            With ThisWorkbook
                .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = NewSht
                With ActiveSheet
                    .Range("A1:B1") = Array("Total Cost", "Quantity")
                    .Range("A1:B1").Font.Bold = True
                    .Range("A2").FormulaR1C1 = _
                    "=SUM(INDEX(R[-1]:R[8],,MATCH(""column ex"",R[-1],0)))"
                    .Columns.AutoFit
                End With
            End With
    
    End Sub


Function WksExists(wksName As String) As Boolean
''/// use to check if a sheet already exists
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

Open in new window

Shyretta Jenkins

ASKER

It worked! Thank you so much for all your time