先填入公式再轉值 寫法如下
Sub sheet1_Formula()
Rn& = [sheet1!B65536].End(3).Row
With [sheet1!A2].Resize(Rn - 1)
.Formula = "=COUNTIF($B$2:$B2,B2)-1" '填入A2的公式
.Value = .Value
End With
End Sub
不用Excel工作表函數的VBA寫法
Sub sheet1_VBA()
Dim Arr, Brr, R&, S$
Arr = Range([A2], [B65536].End(3))
For R = 1 To UBound(Arr)
S = S & "," & Arr(R, 2)
Brr = Split(Mid(S, 2), ",")
Arr(R, 1) = UBound(Filter(Brr, Arr(R, 2)))
Next R
Range([A2], [B65536].End(3)) = Arr
End Sub
發表於 2020-8-1 02:19
| 