Creating Formulas with VBA Macros

[Robert_Conklin]

Good evening,

I am wanting to set up some macros to create formulas for vLookup, xLookup, and sum. I found some online but they define the range in the code. I need the code modified to ask me to select each parameter before it executes the macro. Below is the code I found for creating a SUM formula. Any help would be greatly appreciated.

Sub FORMULA_PROPERTY_SUM()

    'Formula is a string of text wrapped  in quotation marks
    'Starts with a = sign
    
    Range("A7").Formula = "=SUM(A1:A6)"

End Sub

Below is the code for a macro that I use to concatenate any number of cells but it asks me to select the cells I want to concatenate along with other options (absolute reference and separator character).

Option Explicit

'The following 4 macros are used to call the Concatenate_Formula macro.
'The Concatenate_Formula macro has different options, and these 4 macros
'run the Concatenate_Formula macro with different options.  You will want
'to assign any of these macros to a ribbon button or keyboard shortcut.

Sub Concatenate_Options()
    'Creates an CONCATENATE formula and prompts the user for options
    'Options are absolute refs and separator character
    Call Concatenate_Formula(True, True)
End Sub
Sub Concatenate_Formula(bConcat As Boolean, bOptions As Boolean)

Dim rSelected As Range
Dim c As Range
Dim sArgs As String
Dim bCol As Boolean
Dim bRow As Boolean
Dim sArgSep As String
Dim sSeparator As String
Dim rOutput As Range
Dim vbAnswer As VbMsgBoxResult
Dim lTrim As Long
Dim sTitle As String

    'Set variables
    Set rOutput = ActiveCell
    bCol = False
    bRow = False
    sSeparator = ""
    sTitle = IIf(bConcat, "CONCATENATE", "Ampersand")
    
    'Prompt user to select cells for formula
    On Error Resume Next
    Set rSelected = Application.InputBox(Prompt:= _
                    "Select cells to create formula", _
                    Title:=sTitle & " Creator", Type:=8)
    On Error GoTo 0
    
    'Only run if cells were selected and cancel button was not pressed
    If Not rSelected Is Nothing Then
        
        'Set argument separator for concatenate or ampersand formula
        sArgSep = IIf(bConcat, ",", "&")
        
        'Prompt user for absolute ref and separator options
        If bOptions Then
        
            vbAnswer = MsgBox("Columns Absolute? $A1", vbYesNo)
            bCol = IIf(vbAnswer = vbYes, True, False)
            
            vbAnswer = MsgBox("Rows Absolute? A$1", vbYesNo)
            bRow = IIf(vbAnswer = vbYes, True, False)
                
            sSeparator = Application.InputBox(Prompt:= _
                        "Type separator, leave blank if none.", _
                        Title:=sTitle & " separator", Type:=2)
        
        End If
        
        'Create string of cell references
        For Each c In rSelected.Cells
            sArgs = sArgs & c.Address(bRow, bCol) & sArgSep
            If sSeparator <> "" Then
                sArgs = sArgs & Chr(34) & sSeparator & Chr(34) & sArgSep
            End If
        Next
        
        'Trim extra argument separator and separator characters
        lTrim = IIf(sSeparator <> "", 4 + Len(sSeparator), 1)
        sArgs = Left(sArgs, Len(sArgs) - lTrim)

        'Create formula
        'Warning - you cannot undo this input
        'If undo is needed you could copy the formula string
        'to the clipboard, then paste into the activecell using Ctrl+V
        If bConcat Then
            rOutput.Formula = "=CONCATENATE(" & sArgs & ")"
        Else
            rOutput.Formula = "=" & sArgs
        End If
        
    End If

End Sub

 

[rlv01]

A simple example for SUM

Sub FORMULA_PROPERTY_SUM()
    Dim FormulaCell As Range
    Dim SumRange As Range
    Dim FormulaStr As String

    'Input Box 'Type'
    '0  - A Formula
    '1  - A Number
    '2  - Text (a string)
    '4  - A logical value (True or False)
    '8  - A cell reference, as a Range object
    '16 - An error value, such as #N/A
    '64 - An array of values

    On Error Resume Next
    Set FormulaCell = Application.InputBox("Enter Cell for Formula", "Data Entry", , , , , , 8)
    Debug.Print TypeName(FormulaCell)

    Set SumRange = Application.InputBox("Enter Range of Cells to sum", "Data Entry", , , , , , 8)
    Debug.Print TypeName(SumRange)
    On Error GoTo 0

    If Not FormulaCell Is Nothing And Not SumRange Is Nothing Then
        FormulaStr = "=SUM(" & SumRange.Address & ")"
        FormulaCell.Formula = FormulaStr
    End If
End Sub

 

[Robert_Conklin]

A simple example for SUM

Sub FORMULA_PROPERTY_SUM()
    Dim FormulaCell As Range
    Dim SumRange As Range
    Dim FormulaStr As String

    'Input Box 'Type'
    '0  - A Formula
    '1  - A Number
    '2  - Text (a string)
    '4  - A logical value (True or False)
    '8  - A cell reference, as a Range object
    '16 - An error value, such as #N/A
    '64 - An array of values

    [COLOR=rgb(97, 189, 109)]On Error Resume Next
    Set FormulaCell = Application.InputBox("Enter Cell for Formula", "Data Entry", , , , , , 8)
    Debug.Print TypeName(FormulaCell)[/COLOR]

    Set SumRange = Application.InputBox("Enter Range of Cells to sum", "Data Entry", , , , , , 8)
    Debug.Print TypeName(SumRange)
    On Error GoTo 0

    If Not FormulaCell Is Nothing And Not SumRange Is Nothing Then
        FormulaStr = "=SUM(" & SumRange.Address & ")"
        FormulaCell.Formula = FormulaStr
    End If
End Sub

Thank you, rlv01. That is great. Is it possible to change the text in green above to recognize the cell that is already selected?

 

[rlv01]

Thank you, rlv01. That is great. Is it possible to change the text in green above to recognize the cell that is already selected?

Just change this:

Set FormulaCell = Application.InputBox("Enter Cell for Formula", "Data Entry", , , , , , 8)

to this

 Set FormulaCell = ActiveCell

If you mean something else by "the cell that is already selected", you'll have to explain further.

 

[Robert_Conklin]

Just change this:

Set FormulaCell = Application.InputBox("Enter Cell for Formula", "Data Entry", , , , , , 8)

to this

 Set FormulaCell = ActiveCell

If you mean something else by "the cell that is already selected", you'll have to explain further.

Thank you very much. That worked perfectly! I apologize for all of the questions. For the other two formula types (Vlookup and XLookup) would I use this same code structure? I want to learn how to code these myself but can't have trouble understanding what to use where.

 

[rlv01]

The same basic structure adapted to the extra parameters needed for VLOOKUP