asked on

Excel VBA: perform subtraction, summation, multiplication on multiple columns

Hello experts,

I have the following procedure reported above which allows me to add string in between for multiple columns.
I would like to take as a reference to perform the following:
>Be able to perform subtraction, summation, multiplication when value is reported at inputbox 2.
Example: I report *1.6 and the various values concerned by the column should be multiplied by 1.6.
If column involved by the operation is not a number exit sub with the following message:
“Unable to proceed as one of the column involved by the operation is not a number.”

If you have questions, please contact me.
Thank you in advance for your help.

Sub Add_String_In_Between_Multiple_Columns()

    Dim sRng As String
    Dim temp_rng As String
    Dim strSpecificChar As Variant    'New declaration in order to add numeric and non numeric values
    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim intWhich As Integer
    Dim intWhich_temp As String

    On Error GoTo Error_Routine

    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
                          & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If

    
    temp_rng = Application.InputBox("Please enter value that you want to put in between", Type:=1 + 2)

    If VarType(temp_rng) <> vbString Then
        MsgBox ("Cell not a string")
        Exit Sub
    End If
    sRng = temp_rng


    For Each strCol In Split(strColList, ";")
        lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row
        strSpecificChar = sRng

        For lngRow = 2 To lngLastRow
            Cells(lngRow, strCol).Value = strSpecificChar & Cells(lngRow, strCol).Value & strSpecificChar
        Next
    Next
    
    Exit Sub
Error_Routine:
    MsgBox Err.Description, vbExclamation, "Something went wrong!"

End Sub

Open in new window

Saqib Husain

Can you upload a sample file commensurate with the macro?

Martin Liss

Try this.

Sub Do_The_Math()

    Dim strMath As String
    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim dblValue As Double

    On Error GoTo Error_Routine

    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
                          & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If

    
    strMath = Application.InputBox("Please enter the mathematical operator and value." & vbCrLf _
                        & "For example to multiply by 1.6 enter '*1.6'.")

    Select Case Left$(strMath, 1)
        Case "+", "-", "*", "/"
            ' The above are valid
        Case Else
            MsgBox "The first character must be one of these for mathematical operators: '+', '-', '*' or '/'"
            Exit Sub
    End Select
    
    If Len(strMath) = 1 Or Not IsNumeric(Mid(strMath, 2)) Then
        MsgBox "A numeric value must follow the mathematical operator"
        Exit Sub
    End If
    dblValue = CDbl(Mid(strMath, 2))
    
    If Left$(strMath, 1) = "/" And Mid(strMath, 2) = "0" Then
        MsgBox "The mathematical statement would result in division by zero which is not defined."
        Exit Sub
    End If

    For Each strCol In Split(strColList, ";")
        lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row

        For lngRow = 2 To lngLastRow
            If IsNumeric(Cells(lngRow, strCol).Value) Then
                Select Case Left$(strMath, 1)
                    Case "+"
                        Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value + dblValue
                    Case "="
                        Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value - dblValue
                    Case "*"
                        Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value * dblValue
                    Case "/"
                        Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value / dblValue
                End Select
            End If
        Next
    Next
    
    Exit Sub
Error_Routine:
    MsgBox Err.Description, vbExclamation, "Something went wrong!"

End Sub

Open in new window

Luis Diaz

ASKER

Hello Martin,

I tested your proposal and it works for summation, multiplication and division however for subtraction I don't have any operation performed without any blocking message. I tested with the following input values -3.

I supposed that I need to change the following:

Select Case Left$(strMath, 1)
Case "+"
Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value + dblValue
Case "-"
Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value - dblValue
Case "*"
Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value * dblValue
Case "/"
Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value / dblValue
End Select

I also was wondering if we can keep the formula example:

A2=23+4 instead of directly having the value reported: 27

I attached dummy file. Let me know how should I proceed.

Thank you in advance for your help.
DummyFileMatematicalOperationOnMult.xlsm

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

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

Start Free Trial

Luis Diaz

ASKER

Thank you Martin,
I tested and it works!

>but why do you want to do that?

Why I want to do it through the formula? Because this allows me to remember the previous value and if I want the value without the formula I just do a paste special and I select value option.
If your question is why I want to use this procedure.
Because this allows me to smoothly perform maths operations without doing manual actions. Indeed I will add it to my personal Add-In to use it on a regular basis.
Thank you very much for your help Martin!

SOLUTION

Martin Liss

membership

This solution is only available to members.

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

Start Free Trial