首页 文章

使用Application.Selection和Application.InputBox将两列相乘

提问于
浏览 次
0

VBA非常新，想要了解更多 . 我正在尝试使用电子表格中的公式轻松完成的任务，但我想学习如何编写代码 .

我基本上想告诉用户选择两个相同长度的单元格范围并将它们相乘以返回一个值 . 出于我用它的目的，我根据市场价格和股票数量计算股票的市场 Value .

我已经完成了设置，但我不知道我做了什么来将它们相乘或返回值

Sub CalculateMV（）

Dim Rng作为Range Dim Rng2作为范围

设置Rng = Application.Selection Set Rng = Application.InputBox（“Select All Market Values”，“MV Calculator”，Type：= 8）

Dim rng3 As Range

设置rng3 = Application.Selection Set rng3 = Application.InputBox（“Select All Market Values”，“MV Calculator”，Type：= 8）

对于Rng中的每个Rng2

有什么大的（基本上）我缺少，像数组或我需要编写一个函数？

提前致谢 .

arrays function excel-vba vba excel

1 回答

  • 0

    首先，由于您的范围需要大小相同，因此如果范围彼此不相等，或者其中一个范围超出范围，则需要执行检查以要求用户“再试一次” 1列宽 .

    然后，您需要使用 For 循环来乘以每个单元格 . 在这里'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'知道您希望值实际去哪里，但这应该让您开始 .

    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
    回复于

相关问题