請問為何在excel 2010中無法執行

h60327

Private Sub CommandButton1_Click()
[A2:H65536].ClearContents
Dim ds As Object, ds1 As Object, Arr(1 To 65536, 0 To 4), Men, i#, test$, k%, temp$, s&
%0

h60327

Private Sub CommandButton1_Click()
[A2:H65536].ClearContents
Dim ds As Object, ds1 As Object, Arr(1 To 65536, 0 To 4), Men, i#, test$, k%, temp$, s&
Men = Range([J2], [J65536].End(xlUp)).Value
Set ds = CreateObject("Scripting.Dictionary") '國定假日
Set ds1 = CreateObject("Scripting.Dictionary") '補上班

For i = 2 To [M65536].End(xlUp).Row '國定假日
temp = Month(Cells(i, 13)) & "," & Day(Cells(i, 13))
ds.Add temp, i
Next
For i = 2 To [O65536].End(xlUp).Row '補上班
temp = Month(Cells(i, 15)) & "," & Day(Cells(i, 15))
ds1.Add temp, i
Next

For i = [k1] To DateAdd("yyyy", 1, [k1]) - 1
test = Month(i) & "," & Day(i)
   If (Weekday(i, vbMonday) < 6 And ds.Exists(test) = False) Or ds1.Exists(test) = True Then  '週一至週五並扣除M欄國定假日加入補上班日
     s = s + 1
     Arr(s, 1) = i
     Arr(s, 2) = Month(i)
     Arr(s, 3) = Day(i)
     Arr(s, 4) = Weekday(i, 2)
     k = IIf(s Mod UBound(Men) = 0, UBound(Men), s Mod UBound(Men))
     Arr(s, 0) = Men(k, 1)
    End If
Next
[A2].Resize(s, 5) = Arr

Range([E2], [E65536].End(xlUp)).FormulaR1C1 = _
"=IF(WEEKDAY(RC[-3])=2,""一"",(IF(WEEKDAY(RC[-3])=3,""二"",(IF(WEEKDAY(RC[-3])=4,""三"",(IF(WEEKDAY(RC[-3])=5,""四"",(IF(WEEKDAY(RC[-3])=6,""五"",(IF(WEEKDAY(RC[-3])=7,""六"",(IF(WEEKDAY(RC[-3])=1,""日"","""")))))))))))))"
Range([E2], [E65536].End(xlUp)).Formula = Range([E2], [E65536].End(xlUp)).Value

End Sub

執行至For i = 2 To [M65536].End(xlUp).Row  就發生錯誤

h60327

抱歉應該是我表達的不夠清楚，不知為何這段程式碼在excel 2003時運作的很順暢，但是公司最近更新為excel 2010時早期有使用到vba的檔案有些就不能執行了，比如上面的檔案在執行到For i = 2 To [M65536].End(xlUp).Row  就顯示錯誤16 表示運算太複雜，不知是否因版本的問題造成的嗎，有否解決的方式呢?再次感謝大家的幫忙指教

luhpro

回復 3# h60327
我猜有可能是 65536 列號超過系統容許的列號,
試試將 [M65536] 改成 Cells(Rows.Count, 13)
其餘比照處理.

Cells格式 :
Cells(列號, 欄號) = [欄名列號]

h60327

感謝luhpro先進的指導，運算式終於可以往下跑，但執行到For i = [k1] To DateAdd("yyyy", 1, [k1]) - 1這一行時又發生問題了仍然是錯誤16 運算太複雜，因為是屬於日期運算，不知要如何修正，還請各位先進能予指導，非常感謝

h60327

把For i = [k1] To DateAdd("yyyy", 1, [k1]) - 1
改成For i = [k1] To DateAdd("yyyy", 1, Cells(1, 11).Value) - 1
就運作正常了，只是不知原因為何。

stillfish00

回復 6# h60327
i 的宣告  i# 改為 i& 看看

h60327

這太神奇了，但為什麼呢