岩伸 發問時間: 電腦與網際網路軟體 · 4 年前

excel vba 複製貼上 出現應用程式或物件定義錯誤?

Sub 匯入()

Dim i, j As Integer

Set ro = Workbooks("option_output.xlsm").Sheets("tcap-1990")

Set too = Workbooks("總檔.xlsx").Sheets("targetvalue")

For i = 4 To 1000 Step 4

For j = 2 To 400

If ro.Cells(1, i).Find(what:=too.Cells(3, j), LookIn:=xlFormulas) Is Nothing Then

Else

ro.Range(Cells(2, i), Cells(368, i)).Copy _

Destination:=too.Range(Cells(5, j), Cells(371, j))

End If

Next j

Next i

End Sub

請問高手們,我的copy destination到底哪裡出錯了阿???

2 個解答

評分
  • ?
    Lv 7
    4 年前
    最佳解答

    .

    前方先加入這段試試

    Dim ro, too

    若不行的話,再加入一行:

    On Error Resume Next

    IF的部份修改:

    If Not ro.Cells(1, i).Find(what:=too.Cells(3, j), LookIn:=xlFormulas) Is Nothing Then

    ro.Cells(2, i).Resize(367).Copy too.Cells(5, j)

    End If

    以上試試看!

    IF部份亦可分開寫為:

    Set c = ro.Cells(1, i).Find(what:=too.Cells(3, j), LookIn:=xlFormulas)

    If Not c Is Nothing Then ro.Cells(2, i).Resize(367).Copy too.Cells(5, j)

    或改為:

    If ro.Cells(1, i) <> too.Cells(3, j) Then ro.Cells(2, i).Resize(367).Copy too.Cells(5, j)

    再試試

  • 4 年前

    daniel 大請問

    Sub 匯入()

    Dim i, j As Integer

    Dim ro, too As Object

    Set ro = Workbooks("price total.xlsm").Sheets("tcap-1990")

    Set too = Workbooks("論文總檔.xlsx").Sheets("targetvalue")

    On Error Resume Next

    For i = 4 To 1000 Step 4

    For j = 2 To 400

    ro.Cells(1, i).Find(what:=too.Cells(3, j)).Activate

    ro.Cells(2, i).Resize(367).Copy _

    Destination:=too.Cells(5, j)

    Next j

    Next i

    End Sub

    目前改成這樣後,就只會把第一筆資料複製到其他的欄位上

還有問題?馬上發問,尋求解答。