我有一个36K行的电子表格。36K行中的每一行将与大约350个数字中的一个匹配。当这个数字匹配时,我将把文本和冒号输入到与匹配数字同一行的另一列中。我的问题是:
- 如何让宏在一个范围内运行(例如:T2:T3600)并为每一行返回一个值
- 我可以按列而不是按行范围来做这件事吗。(例如:名为Category的列,而不是T2:T3600)。原因是每列中的行数都会发生变化
这一次只适用于一行,但我不想对每一行都这样做。我意识到我必须为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
提前谢谢。
使用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
有了350多个值来检查30000行数据,您最好将其作为另一个(隐藏)工作表上的表进行索引,并使用WorksheetFunction.VLookup
进行查找,而不是像这样强制执行Case开关。
在这种情况下,您可以完全省略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
我不确定哪一个会针对这种用途进行优化。