VBA 将两个命名范围相乘 [英] VBA multiply two named ranges

问题描述

我一直在到处寻找这个问题，但似乎找不到答案或让它发挥作用.

I've been trying to look this up everywhere and I can't seem to find the answer or get it to work.

我有代码

'Define Variables for cell ranges'
Dim BidItem As Range
Dim BidItemDes As Range
Dim BidItemUnit As Range
Dim BidItemQTY As Range
Dim BidItemUP As Range

'Store the sheet range into the variables'
Set BidItem = Sheets("BidItems").Range("A1:A" & Range("A1").End(xlDown).Row)
Set BidItemDes = Sheets("BidItems").Range("B1:B" & Range("B1").End(xlDown).Row)
Set BidItemUnit = Sheets("BidItems").Range("C1:C" & Range("C1").End(xlDown).Row)
Set BidItemQTY = Sheets("BidItems").Range("D1:D" & Range("D1").End(xlDown).Row)       
Set BidItemUP = Sheets("BidItems").Range("E1:E" & Range("E1").End(xlDown).Row)
Set BidItemValue = Sheets("BidItems").Range("F1:F" & Range("F1").End(xlDown).Row)

Set BidItemValue = Sheets("BidItems").Range("F1:F" & Range("F1").End(xlDown).Row)

我需要做的是将 BidItemQTY 范围内的所有数据乘以范围 BidItemUP然后将该答案输出到范围 BidItemValue.

What I need to do is have all the data in range BidItemQTY and Multiply it by the range BidItemUP and then output that answer to the range BidItemValue.

我设置了最后一行代码来启动该功能，但是我似乎无法掌握如何使用变量在 VBA 中执行数学函数.

I have the last line of code setup to start the function but I can't seem to grasp on how to do math functions in VBA with Variables.

推荐答案

考虑这个小例子:

Sub dural()
    Dim second As Range
    Dim first As Range, prodt As Long

    Set first = Range("A1:A3")
    Set second = Range("B1:B3")
    prodt = Application.WorksheetFunction.SumProduct(first, second)

    MsgBox prodt
End Sub

编辑#1:

要获取存储在单元格中的单个产品，请使用:

To get the individual products stored in cells, use:

Sub dural()
    Dim second As Range
    Dim first As Range, prodt As Long
    Dim third As Range

    Set first = Range("A1:A3")
    Set second = Range("B1:B3")
    Set third = Range("C1:C3")

    For i = 1 To 3
        third(i, 1) = first(i, 1) * second(i, 1)
    Next i

End Sub

注意事项:

• 由于范围不是单个单元格，我们将它们视为二维的
• 使用 Transpose()
可以避免循环
• 在这种情况下，一维也适用:

For i = 1 To 3
        third(i) = first(i) * second(i)
Next i

这篇关于VBA 将两个命名范围相乘的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！