|
看了好久才搞明白,主要是你那个字典用的让我很迷惑。改成下面的代码:
你字典被我注释掉了,我觉得用不上。字典用于不确定的多条件,你这只有固定的2-3个条件,直接 if 就行了;
其他的改动还有:
1) 循环表: For m = 1 To Worksheets.Count
2)引用表: With Worksheets(m)
3)参数转数字: s = Val(arr(i, 1))
这是为了防止文本和数字格式的混用,数字0和文本0外观看不出来差别;
4)brr数组扩展:ReDim brr(1 To UBound(arr), 1 To 2)
你要填充b\c两列,所以扩展定义成2列;
Dim i%, d, arr, brr()
' Set d = CreateObject("scripting.dictionary")
' d(0) = "a": d(1) = "a": d(2) = "b": d(3) = "b"
For m = 1 To Worksheets.Count
With Worksheets(m)
arr = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
ReDim brr(1 To UBound(arr), 1 To 2)
For i = 1 To UBound(arr)
' If d.Exists(arr(i, 1)) Then brr(i, 1) = d(arr(i, 1))
s = Val(arr(i, 1))
If s = 0 Or s = 1 Then
brr(i, 1) = "a"
brr(i, 2) = "c"
End If
If s = 2 Or s = 3 Then
brr(i, 1) = "b"
brr(i, 2) = "d"
End If
Next
.Range("B1:c" & .Cells(.Rows.Count, 2).End(xlUp).Row).ClearContents
.[B1].Resize(UBound(brr), 2) = brr
End With
Next m
|
|