Using Application.Selection and Application.InputBox to Multiply Two Columns

Question

Very new to VBA, wanting to learn more. I'm trying something that I could easily do using formulas in a spreadsheet, but I want to learn how to code it.

I basically want to tell the user to select two cell ranges of the same length and multiply them together to return a value. For the purposes of what I would use it for, I'm calculating market values of stocks given the market price and number of shares.

I've the set up, but I don't know what I do to multiply together or return the value

Sub CalculateMV()

Dim Rng As Range Dim Rng2 As Range

Set Rng = Application.Selection Set Rng = Application.InputBox("Select All Market Values", "MV Calculator", Type:=8)

Dim rng3 As Range

Set rng3 = Application.Selection Set rng3 = Application.InputBox("Select All Market Values", "MV Calculator", Type:=8)

For Each Rng2 In Rng

Is there something large (fundamentally) that I'm missing, like an array or I need to write a function?

Thanks in advance.

I should mention that I'm trying to return a value for each cell*cell multiplication. — fordo19

BobbitWormJoe BobbitWormJoe · Accepted Answer · 2014-12-08T23:51:57

First of all, since your ranges need to be the same size, you'll need to implement a check to ask the user to "try again" if the ranges don't equal each other, or if one of the ranges is more than 1 column wide.

You will then need to use a For loop to multiply each cell. Here's the code I came up with. Currently it only returns the values to the debug/immediate window (press Ctrl+G in VBA to view it), since I don't know where you want the values to actually go, but this should get you started.

Sub CalculateMV()

    On Error Resume Next

    Dim Rng1 As Range, Rng2 As Range
    Dim RowCount As Long, i As Long
    Dim Result As Double

Lbl_TryAgain1:
    Set Rng1 = Application.InputBox("Select All Market Values, 1st Range", "MV Calculator", Type:=8)
    If Rng1.Columns.Count > 1 Then
        MsgBox "Each range must have a maximum of one column. Please try again.", vbExclamation
        Set Rng1 = Nothing
        GoTo Lbl_TryAgain1
    End If
Lbl_TryAgain2:
    Set Rng2 = Application.InputBox("Select All Market Values, 2nd Range", "MV Calculator", Type:=8)
    If Rng2.Columns.Count > 1 Then
        MsgBox "Each range must have a maximum of one column. Please try again.", vbExclamation
        Set Rng2 = Nothing
        GoTo Lbl_TryAgain2
    ElseIf Rng1.Rows.Count <> Rng2.Rows.Count Then
        MsgBox "Each range must have the same number of rows. Please try again.", vbExclamation
        Set Rng2 = Nothing
        GoTo Lbl_TryAgain2
    End If

    RowCount = Rng1.Rows.Count

    For i = 1 To RowCount
        Result = Val(Rng1.Cells(i, 1)) * Val(Rng2.Cells(i, 1))
        Debug.Print Result
    Next i

End Sub

Using Application.Selection and Application.InputBox to Multiply Two Columns

1 Answers