Question

EXCEL VBA 代码的问题？

C9是AQ9:AT9数值的除3余0的个数;
D9是AQ9:AT9数值的除3余1的个数;
E9是AQ9:AT9数值的除3余2的个数;
一行一行相对应的;
请老师帮忙,写个快速返回的数组代码,让这些余数值返回在图中的位置.

Answer 1

这个简单，好写，。。。

没注意，要数组！！！！~~~~，修改如下

Sub cm()
Dim r, i, j%
Dim a, b, c%
Dim arr, brr, sh1
Set sh1 = ActiveSheet
With sh1
r = .Range("at" & Rows.Count).End(xlUp).Row
.Range("c9:e" & Rows.Count).ClearContents
arr = .Range("aq9:at" & r)
ReDim brr(1 To UBound(arr), 1 To 3)
For i = 9 To r
a = 0
b = 0
c = 0
For j = 43 To 46
If .Cells(i, j) Mod 3 = 0 Then
a = a + 1
ElseIf .Cells(i, j) Mod 3 = 1 Then
b = b + 1
Else
c = c + 1
End If
brr(i - 8, 1) = a
brr(i - 8, 2) = b
brr(i - 8, 3) = c
Next j
Next i
.Range("c9").Resize(UBound(arr), 3) = brr
End With
End Sub

追问

辛苦老师了,这一条 类型不匹配 啊
If .Cells(i, j) Mod 3 = 0 Then

追答

是不是有文本，具体要看到文档才能判断，如果时文本原因，修改如下

Sub cm()
Dim r, i, j%
Dim a, b, c%
Dim arr, brr, sh1
Set sh1 = ActiveSheet
With sh1
r = .Range("at" & Rows.Count).End(xlUp).Row
.Range("c9:e" & Rows.Count).ClearContents
arr = .Range("aq9:at" & r)
ReDim brr(1 To UBound(arr), 1 To 3)
For i = 9 To r
a = 0
b = 0
c = 0
For j = 43 To 46
If IsNumeric(.Cells(i, j)) Then
If .Cells(i, j) Mod 3 = 0 Then
a = a + 1
ElseIf .Cells(i, j) Mod 3 = 1 Then
b = b + 1
Else
c = c + 1
End If
brr(i - 8, 1) = a
brr(i - 8, 2) = b
brr(i - 8, 3) = c
End If
Next j
Next i
.Range("c9").Resize(UBound(arr), 3) = brr
End With
End Sub

追问

非常感谢,就是这个原因.不过我那个没有文本,只是公式返回的数值类型.
还想问一个事,这个是除3余数的,主要是判断方法,那么除5余数要怎么修改一下?

追答

所有Mod 3 替换为 mod 5

追问

你这个判断只有 3余0和3余1,如果换成5,有5种,还要再加判断吧?要不然另外2列是错误值啊
If .Cells(i, j) Mod 3 = 0 Then
a = a + 1
ElseIf .Cells(i, j) Mod 3 = 1 Then
b = b + 1
Else

追答

余0，1，2，3，4？

追问

除5余数是余0,1,2,3,4
而余3余数只有余0,1,2
所以全部替换成除5,少了2列

追答

Set sh1 = ActiveSheet
With sh1
r = .Range("at" & Rows.Count).End(xlUp).Row
.Range("c9:e" & Rows.Count).ClearContents
arr = .Range("aq9:at" & r)
ReDim brr(1 To UBound(arr), 1 To 5)
For i = 9 To r
a = 0
b = 0
c = 0
d = 0
e = 0
For j = 43 To 46
If IsNumeric(.Cells(i, j)) Then
If .Cells(i, j) Mod 5 = 0 Then
a = a + 1
ElseIf .Cells(i, j) Mod 5 = 1 Then
b = b + 1
ElseIf .Cells(i, j) Mod 5 = 2 Then
c = c + 1
ElseIf .Cells(i, j) Mod 5 = 3 Then
d = d + 1
Else
e = e + 1
End If
brr(i - 8, 1) = a
brr(i - 8, 2) = b
brr(i - 8, 3) = c
brr(i - 8, 4) = d
brr(i - 8, 5) = e
End If
Next j
Next i
.Range("c9").Resize(UBound(arr), 5) = brr
End With
End Sub