vba 在单元格范围内选择 CASE / CASE
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/26395251/
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
Select CASE / CASE over range of cells
提问by Rolly Neve
I have a spreadsheet with 36K lines. Each of the 36K lines will be matched to one of about 350 numbers. When that number is matched, I will have text and colons input into another column on the same row as the matching number. The questions I have are:
我有一个 36K 行的电子表格。36K 行中的每一行都将与大约 350 个数字之一匹配。当该数字匹配时,我会将文本和冒号输入到与匹配数字相同行的另一列中。我的问题是:
- How can I have the macro run over a range (Example: T2:T36000) and return a value for each row?
- Can I do this by column instead of the row range. (Example: Column called Category, instead of T2:T36000). The reason for this is that the number of rows in each column will be changing.
- 如何让宏在一个范围内运行(例如:T2:T36000)并为每一行返回一个值?
- 我可以按列而不是行范围执行此操作吗?(例如:名为 Category 的列,而不是 T2:T36000)。这样做的原因是每列中的行数会发生变化。
This works for one row at a time, but I don't want to have to do this for each row. I realize I will have to put values for each of the 350 different numbers.
这一次适用于一行,但我不想对每一行都这样做。我意识到我必须为 350 个不同数字中的每一个输入值。
Sub CategoryChanger()
Select Case Range("AS2").Value
Case 1492
Range("T2") = "IT DOES NOT WORKS"
Case 1491
Range("T2") = "IT WORKS"
End Select
End Sub
Thanks ahead of time.
提前致谢。
回答by David Zemens
Basic loop iteration using For Each ... Next
statement:
使用For Each ... Next
语句的基本循环迭代:
Sub CategoryChanger()
Dim rng as Range
Dim r as Range
Dim result as String
'## Define a range to represent the cells over which you would like to iterate:
'## Modify as needed...
Set rng = Range("AS2:AS100")
'## Iterate each cell in the Range defined "rng"
For Each r in rng.Cells
Select Case r.Value
Case 1492
result = "IT DOES NOT WORKS"
Case 1491
result = "IT WORKS"
End Select
'## Print result in the cell 10 columns to right
'## Modify as needed
r.Offset(0, 10).Value = result
Next
End Sub
With 350+ values to check against 30,000 rows of data, you may be better served to index this as a table on another (hidden) worksheet and use the WorksheetFunction.VLookup
to do the lookup, rather than forcing through a Case switch like this.
使用 350 多个值来检查 30,000 行数据,您最好将其作为另一个(隐藏)工作表上的表进行索引,并使用WorksheetFunction.VLookup
来进行查找,而不是像这样强制通过 Case 开关。
In that case you would omit the Select Case
block altogether, and simply do like so (assuming you add a worksheet named "Lookup" and put your lookup table in range A1:B350):
在这种情况下,您将Select Case
完全省略该块,只需这样做(假设您添加一个名为“Lookup”的工作表并将您的查找表放在范围 A1:B350 中):
Sub CategoryChanger()
Dim rng as Range
Dim r as Range
Dim result as String
'## Define a range to represent the cells over which you would like to iterate:
'## Modify as needed...
Set rng = Range("AS2:AS100")
'## Iterate each cell in the Range defined "rng"
For Each r in rng.Cells
On Error Resume Next
result = Application.WorksheetFunction.VLookup(r.Value, Worksheets("Lookup").Range("A1:B350"), 2, False)
If Err.Number <> 0 Then result = "NOT FOUND!"
On Error GoTo 0
'## Print result in the cell 10 columns to right
'## Modify as needed
rng.Offset(0, 10).Value = result
'Clear out the "result" value for the next iteration:
result = vbNullstring
Next
End Sub
I am not sure which would be optimized for this use.
我不确定哪个会针对此用途进行优化。