軟體:excel2010
大大拍寫
之前詢問陣列公式寫成VBA已經成功(灑花)
但是第2筆之後的發現數值是錯誤的
看VBA逐筆運算式，迴圈數值應該是正確的
但不知為何跑錯誤的值
檔案/示意圖/VBA如下
檔案http://t.cn/RnPe5vE
示意圖https://imgur.com/a/9MhAk
Sub test()
'判斷地址不重複數量
ActiveSheet.Range("A1").Select
While ActiveCell.Value <> ""
kkk = ActiveCell.Row
ActiveSheet.Range("A" & kkk).Select
ActiveSheet.Range("V" & kkk) = "地址數"
ActiveSheet.Range("T" & kkk).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
ADress_Row = Selection.Rows.Count '判斷地址的區間
ActiveSheet.Range("V" & kkk + 1).Value = Application.Evaluate _
("=SUM(If(" & Range("T" & kkk + 1 & ":U" & ADress_Row).Address &
"<>"""",1/COUNTIF(" & Range("T" & kkk + 1 & ":U" & ADress_Row).Address & ","
& _
Range("T" & kkk + 1 & ":U" & ADress_Row).Address & ")))")
ActiveSheet.Range("A" & kkk + 1).Select
'判斷選擇下個檔案名稱在哪裡
For kkk = ActiveCell.Row To ActiveCell.SpecialCells(xlLastCell).Row
Step 1
If ActiveSheet.Range("A" & kkk) <> "" Then Exit For
Next kkk
ActiveSheet.Range("A" & kkk).Select
Wend
End Sub