Excel VBA 在列中查找值，并进行数学运算
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19458274/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Find value in column, and do math
提问by Stliiyone
I have
我有
Column A - with Names
Column B - with Quantities
Column C - Where I want returned Value of Quantity x Cost
Column E - with Names but located in different cells
Column F - with Prices
A 列 - 名称
B 列 - 数量
C 列 - 我想要返回的数量值 x 成本
列 E - 带有名称，但位于不同的单元格
F 列 - 价格
What I'm trying to achieve is: Take value from A1and find it in E:E(Lets say we found it in E10), when found take value of B1and multiply it by Respective value of F10- and put all this in Column C
我想要实现的是：从A1 中获取值并在E:E 中找到它（假设我们在E10 中找到它），找到时取B1 的值并将其乘以F10 的相应值- 并将所有这些放入C栏
And so on for all values in column A
依此类推A 列中的所有值
I was trying to do it with Do while and two variables x and y, but for some reason it doesn't find all values only for some rows.
我试图用 Do while 和两个变量 x 和 y 来做，但由于某种原因，它没有找到仅针对某些行的所有值。
Thank you in Advance.
先感谢您。
Sub update_button()
'calculates money value for amazon sku
Dim x, y, z As Integer 'x, y, and z variables function as loop counters
'Loop through added SKU/Prices
For x = 4 To 25000
If Worksheets("Sheet1").Range("H" & x) = "" Then
'Blank row found, exit the loop
Exit For
End If
'Loop through Column E to find the first blank row to add the value from H into
For y = 4 To 25000
If Worksheets("Sheet1").Range("E" & y) = "" Then
'Blank row found, Add SKU and Price
Worksheets("Sheet1").Range("E" & y) = Worksheets("Sheet1").Range("H" & x)
Worksheets("Sheet1").Range("F" & y) = Worksheets("Sheet1").Range("I" & x)
'Blank out Columns H and I to prevent need to do it manually
Worksheets("Sheet1").Range("H" & x) = ""
Worksheets("Sheet1").Range("I" & x) = ""
Exit For
End If
Next y
Next x
'---NOW THIS IS WHERE I HAVE THE PROBLEM
'Get Values
Dim intCumulativePrice As Integer
'Loop through report tab and get SKU
x = 4 'initialize x to the first row of data on the Sheet1 tab
Do While Worksheets("Sheet1").Range("A" & x) <> "" 'Loop through valid SKU's to find price of item
y = 4 'initialize y to the first row of SKUs on the Sheet1 tab
Do While Worksheets("Sheet1").Range("E" & y) <> ""
If Worksheets("Sheet1").Range("E" & x) = Worksheets("Sheet1").Range("A" & y) Then 'Check if current SKU on Sheet1 tab matches the current SKU from SKU list
'Calculates the total
intCumulativePrice = intCumulativePrice + (Worksheets("Sheet1").Range("B" & y) * Worksheets("Sheet1").Range("F" & x))
' Puts Quantity X Price in Column B agains every Cell
Worksheets("Sheet1").Range("C" & y) = (Worksheets("Sheet1").Range("B" & y) * Worksheets("Sheet1").Range("F" & x))
Exit Do
End If
y = y + 1
Loop
x = x + 1
Loop
'Puts Grand total in Column L Cell 4
Worksheets("Sheet1").Range("L4") = intCumulativePrice
'Show messagebox to show that report processing has completed
MsgBox "Report processing has been completed successfully", vbInformation, "Processing Complete!"
End Sub
回答by Stewbob
You can do this with a simple VLOOKUPformula in column C.
您可以使用VLOOKUPC 列中的简单公式来完成此操作。
=VLOOKUP(A1,E1:F65000,2,FALSE)*B1
You can also use a named range for the data in columns E and F, so you don't have to rely on a fixed address like E1:F65000.
您还可以为 E 列和 F 列中的数据使用命名范围，因此您不必依赖像E1:F65000.
回答by chris neilsen
To do this with VBA you should copy the source data to Variant arrays and loop over those. Much faster and IMO easier to read and debug.
要使用 VBA 执行此操作，您应该将源数据复制到 Variant 数组并循环遍历这些数据。更快，IMO 更易于阅读和调试。
Something like this
像这样的东西
Sub Demo()
Dim Dat As Variant
Dim PriceList As Range
Dim PriceListNames As Variant
Dim PriceListPrices As Variant
Dim Res As Variant
Dim sh As Worksheet
Dim i As Long
Dim nm As String
Dim nmIdx As Variant
Dim FirstDataRow As Long
FirstDataRow = 4
Set sh = ActiveSheet
With sh
Dat = Range(.Cells(FirstDataRow, "B"), .Cells(.Rows.Count, "A").End(xlUp))
Set PriceList = Range(.Cells(FirstDataRow, "E"), .Cells(.Rows.Count, "F").End(xlUp))
PriceListNames = Application.Transpose(PriceList.Columns(1)) ' Need a 1D array for Match
PriceListPrices = PriceList.Columns(2)
ReDim Res(1 To UBound(Dat, 1), 1 To 1)
For i = 1 To UBound(Dat, 1)
nm = Dat(i, 1)
nmIdx = Application.Match(nm, PriceListNames, 0)
If Not IsError(nmIdx) Then
Res(i, 1) = Dat(i, 2) * PriceListPrices(nmIdx, 1)
End If
Next
Range(.Cells(FirstDataRow, 3), .Cells(UBound(Dat, 1) + FirstDataRow - 1, 3)) = Res
End With
End Sub
回答by Reafidy
Try this:
尝试这个：
Sub HTH()
With Worksheets("Sheet1")
With .Range("A4", .Range("A" & Rows.Count).End(xlUp)).Offset(, 2)
.Formula = "=VLOOKUP(A4,E:F,2,FALSE)*$B"
.Value = .Value
End With
End With
End Sub
