- 帖子
- 55
- 主題
- 1
- 精華
- 0
- 積分
- 81
- 點名
- 0
- 作業系統
- win
- 軟體版本
- 10
- 閱讀權限
- 20
- 註冊時間
- 2016-5-15
- 最後登錄
- 2018-11-1
|
回復 18# PKKO
P大，哎呀呀，抱歉咧
應該又試錯了，一樣80秒@@
改成下面這樣是72秒，所以 if 內日期轉換需 8秒
myCols = 123
myRows = 7923
Rng = [A3].Resize(myRows, myCols).Value
For j = 1 To (myRows - 2)
zc = ""
For i = 1 To myCols
za = Rng(j, i)
zc = zc & "'" & za & "',"
Next i
zc = Left(zc, Len(zc) - 1)
rez = "(" & zc & ")"
ret = ret & rez & ","
DoEvents
Next j-
- Application.ScreenUpdating = False
- StartTime = Timer
- myCols = Range("A2").CurrentRegion.Columns.Count '計算所有的列
- myRows = Range("A1").CurrentRegion.Rows.Count '計算所有的行
- Rng = [A3].Resize(myRows, myCols).Value
- For j = 1 To (myRows - 2)
- zc = ""
- For i = 1 To myCols
- za = Rng(j, i)
- If za = "" Then '空格轉換
- za = 0
-
- ElseIf i = 2 Then '日期轉換
- If (Len(Split(za, "/")(1)) = 1) And (Len(Split(za, "/")(2)) = 1) Then
- za = Split(za, "/")(0) & "-0" & Split(za, "/")(1) & "-0" & Split(za, "/")(2)
- ElseIf Len(Split(za, "/")(1)) = 1 Then
- za = Split(za, "/")(0) & "-0" & Split(za, "/")(1) & "-" & Split(za, "/")(2)
- ElseIf Len(Split(za, "/")(2)) = 1 Then
- za = Split(za, "/")(0) & "-" & Split(za, "/")(1) & "-0" & Split(za, "/")(2)
- Else
- za = Split(za, "/")(0) & "-" & Split(za, "/")(1) & "-" & Split(za, "/")(2)
- End If
-
- End If
- zc = zc & "'" & za & "',"
- Next i
- zc = Left(zc, Len(zc) - 1) '刪除最後一個","
- rez = "(" & zc & ")"
- ret = ret & rez & ","
- DoEvents
- Next j
- EndTime = Timer
- MsgBox Format(EndTime - StartTime, "00.00") & "秒"
- Application.ScreenUpdating = True
|
|
發表於 2017-1-2 01:15
| 