VBA统计数值出现次数

lijian8003

附件中，用COUNTIF函数统计依次递减区域内某数值的出现个数，如何用VBA实现？

liulang0808

Sub 按钮1_Click()
    r = Cells(Rows.Count, 2).End(3).Row
    For j = 3 To r
        For i = 1 To 14
            Cells(j, 26 + i) = 3 - WorksheetFunction.CountIf(Cells(j - 2, 2).Resize(3, 10), Cells(1, 11 + i))
        Next i
    Next j
End Sub

liulang0808

附件内容供参考。。。。。

mafect

经典致敬，需要好好学习

xiangbaoan

Sub test1()
  Dim ar, i, j, k, x, y
  ar = Range("A1").CurrentRegion.Columns(2).Resize(, 10)
  ReDim br(1 To UBound(ar) - 2, 1 To 14) As Long
  For j = 1 To UBound(br, 2)
    For i = 1 To UBound(br)
      br(i, j) = 3
  Next i, j
  For k = 3 To UBound(ar)
    y = y + 1
    For i = k - 2 To k
      For j = 1 To UBound(ar, 2)
        x = Val(ar(i, j))
        If x Then br(y, x) = br(y, x) - 1
      Next
    Next
  Next
  Range("L3").Resize(y, UBound(br, 2)) = br
End Sub