|
有两段不连续数据,我合并为一个range,然后用countif函数查找个数,结果提示错误。如果是连续的两段数据却可以合并且用函数计算,请高手帮忙解答。
数据代码:
dim r1,r2,rr as range
set r1=range(cells(1,1),cells(4,1))
set r2=range(cells(6,1),cells(10,1)) '如果改为“set r2=range(cells(5,1),cells(10,1))”则可以正常运行
set rr=application.union(r1,r2)
for gs=1 to 5
cells(gs,2)=gs
cells(gs,3)=application.worksheetFuction.countif(rr,"="&gs)
next gs
本帖最后由 adders 于 2015-2-2 10:44 编辑
CountIf does work with 不连续数据.
Change code to:
Sub pxt()
Dim r1 As Range, r2 As Range, rr As Range
Dim gs As Long, i As Long
Set r1 = Range(Cells(1, 1), Cells(4, 1))
Set r2 = Range(Cells(6, 1), Cells(10, 1))
Set rr = Application.Union(r1, r2)
For gs = 1 To 5
Cells(gs, 2) = gs
With Application.WorksheetFunction
If rr.Areas.Count = 1 Then
Cells(gs, 3) = .CountIf(rr, gs)
Else
Cells(gs, 3) = 0
For i = 1 To rr.Areas.Count
Cells(gs, 3) = Cells(gs, 3) + .CountIf(rr.Areas(i), gs)
Next i
End If
End With
Next gs
End Sub
|
|