在vba中为for循环中的变量分配数组值 [英] Assigning an array value to a variable inside a for loop in vba
问题描述
我有以下代码(代码1),它工作正常。它只是根据组合框的值将产品及其价格分配给单元格。我想要为每个...循环使用一个缩短代码,以防我的客户端有数千种产品。
I have the following code (CODE 1) and it works fine. It simply assigns the product and its price to cells based on the value of the combo box. I thought of using a for each...loop to shorten the code in case my client has thousands of products.
CODE 2是我的实验性挖掘。但是,当我将数组元素的值赋给循环内的变量时,VBA会给我一个运行时错误1004类型不匹配?此外,我如何能够为下一个循环完成我的二维数组的目标?
CODE 2 is my experimental dig at this. However, when I assign the value of an array element to a variable inside the loop, VBA is giving me a run-time error 1004 type mismatch? Also, how do I could the for next...loop to accomplish my objective for a two dimensional array?
任何人都可以帮助?过去三天,我一直在寻找答案,我找不到任何答案。感谢: - )
Can anyone help? I've been searching for answers all over for the past three days and I can't find any. Thanks :-)
''CODE 1
Private Sub Product1ComboBox_Change()
'Fills in Product and Price columns.
If Sheet1.Product1ComboBox.Value = "1-2-3 ABC" Then
Sheet1.Range("H2").Value = "1-2-3 ABC"
Sheet1.Range("I2").Value = "150.00"
ElseIf Sheet1.Product1ComboBox.Value = "1-3 Pick Up Sticks" Then
Sheet1.Range("H2").Value = "1-3 Pick Up Sticks"
Sheet1.Range("I2").Value = "89.00"
ElseIf Sheet1.Product1ComboBox.Value = "Meat and Potatoes" Then
Sheet1.Range("H2").Value = "Meat and Potatoes"
Sheet1.Range("I2").Value = "140.00"
ElseIf Sheet1.Product1ComboBox.Value = "Pigs in a Blanket" Then
Sheet1.Range("H2").Value = "Pigs in a Blanket"
Sheet1.Range("I2").Value = "140.00"
Else
Sheet1.Range("H2").Value = "Simply Toasted"
Sheet1.Range("I2").Value = "65.00"
End If
'Computes amount.
Sheet1.Range("J2").Value = Sheet1.Range("I2").Value * Sheet1.Qty1ComboBox.Value
End Sub
''CODE 2
Private Sub Product1ComboBox_Change()
Dim Products(1 To 5)
Dim i
Dim Product
Products(1) = "1-2-3 ABC--150"
Products(2) = "1-3 Pick Up Sticks--89"
Products(3) = "Meat and Potatoes--140"
Products(4) = "Pigs in a Blanket--140"
Products(5) = "Simply Toasted--65"
For Each i In Products
Product = Products(i) 'PROBLEM: RUN-TIME ERROR 13 TYPE MISMATCH.
If Products(i) = Sheet1.Product1ComboBox.Value Then
Sheet1.Range("H2").Value = Products(i) 'PROBLEM: RUN-TIME ERROR 13 TYPE MISMATCH.
Exit For
End If
Next i
'Computes amount.
Sheet1.Range("J2").Value = Sheet1.Range("I2").Value * Sheet1.Qty1ComboBox.Value
End Sub
推荐答案
您的问题是 For Each i In Products
这样做是将产品
的每个元素的值分配给 i
。然后,当您使用产品(i)
时,您实际上说,例如产品(1-2-3 ABC - 150)
这是无关紧要的。
What this does is assign the value of each element of Products
to i
in turn. Then when you use Products(i)
you are in effect saying, eg Products("1-2-3 ABC--150")
which is of cource nonsence.
尝试替换
For i = LBound(Products) to UBound(Products)
或
For Each Product In Products
If Product = Sheet1.Product1ComboBox.Value Then
Sheet1.Range("H2").Value = Product
Exit For
End If
Next
这篇关于在vba中为for循环中的变量分配数组值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!