當行中出現指定字串時，VBAExcel將資料從一個作業簿復制到另一個作業簿

我的情況如下圖所示（作業簿 1）：

及以下（練習冊 2）

我想將我的記錄從作業簿 1 復制到作業簿 2，如果

• 在作業簿 1 列 A 中出現字串“surveyor”
• 來自 B 列的值，該列恰好位于找到字串“suveyor”的同一行。

然后我想將此值復制到我的作業簿 2。

我準備了這樣的代碼：

 Sub FrontsheetAdd3()
 Dim x As Worksheet, y As Worksheet, sPath As String
 Dim i As Long

 sPath = ThisWorkbook.Path & "\Survey_form.csv"
 Set x = Workbooks.Open(sPath)

 Set y = ActiveWorkbook.Sheets("Frontsheet") 'set to current worksheet name

'Name of the sheet is the same as Name of the workbook 1
 If x.Sheets("Survey_form").Range("A" & i).Value = "surveyor" Then
 x.Sheets("Survey_form").Rage("B" & i).Value = ("A" & i)
 y.Sheets("Frontsheet").Range("D34").PasteSpecial
 End If
 Next i

 End Sub

我有一個錯誤：

未找到方法或資料成員

在線

   If x.Sheets("Survey_form").Range("A" & i).Value = "surveyor" Then

更新：

更改我的代碼后，現在看起來像這樣：

 Sub FrontsheetAdd3()
 Dim x As Workbook, y As Workbook, sPath As String
 Dim i As Long

 sPath = ThisWorkbook.Path & "\Survey_form.csv"
 Set x = Workbooks.Open(sPath)

 Set y = ActiveWorkbook.Sheets("Frontsheet") 'set to current worksheet name

 'Name of the sheet is the same as Name of the workbook 1
  For i = 1 To 40
  If x.Sheets("Survey_form").Range("A" & i).Value = "surveyor" 
   Then
  x.Sheets("Survey_form").Rage("B" & i).Value = ("A" & i)
  y.Sheets("Frontsheet").Range("D34").PasteSpecial
  End If
  Next i

  End Sub

在線：

      Set y = ActiveWorkbook.Sheets("Frontsheet") 'set to current worksheet name

我的活動作業簿（Workbook2），宏的目的是關閉和錯誤下標超出范圍出現。

那什么是missig呢？

uj5u.com熱心網友回復：

請嘗試下一個改編的代碼。它將從活動的 csv 檔案中復制并退出回圈：

Sub FrontsheetAdd3()
 Dim x As Workbook, y As Worksheet, ws As Worksheet, sPath As String, i As Long

  sPath = ThisWorkbook.path & "\Survey_form.csv"

  Set y = ActiveWorkbook.Sheets("Frontsheet") 'set to current worksheet name
  Set x = Workbooks.Open(sPath): Set ws = x.Sheets(1)

  For i = 1 To 40
    If ws.Range("A" & i).value = "surveyor" Then
        y.Range("D34").value = ws.Rage("B" & i).value: Exit For
    End If
  Next i
End Sub

uj5u.com熱心網友回復：

VBA 查找

• 使用Option Explicit它強制您宣告所有變數。
• 使用變數（更多）使代碼更具可讀性。
• 使用有意義的變數名：sPath是一個很好的名字，x而y用于作業簿則很糟糕。
• 而不是回圈，使用Application.Match.
• 您基本上可以通過三種方式進行復制：Copy, Copy with PasteSpecial或Copy by Assignment ( dCell.Value = sCell.Value) 后者在僅復制值時最有效。

Option Explicit

Sub FrontsheetAdd3()

    Dim dwb As Workbook: Set dwb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = dwb.Worksheets("Frontsheet")
    Dim dCell As Range: Set dCell = dws.Range("D34")
    
    Dim sPath As String: sPath = dwb.Path & "\Survey_form.csv"
    Dim swb As Workbook: Set swb = Workbooks.Open(sPath)
    Dim sws As Worksheet: Set sws = wb.Worksheets("Survey_form")
    ' Determine the position of the first occurence of "surveyor" in column 'A'.
    Dim sIndex As Variant
    sIndex = Application.Match("surveyor", sws.Columns("A"), 0)
    
    If IsNumeric(sIndex) Then ' "suveyor" was found
        Dim sCell As Range: Set sCell = sws.Rows(sIndex).Columns("B")
        dCell.Value = sCell.Value
    Else ' "surveyor" was not found
        dCell.Value = ""
    End If

    swb.Close SaveChanges:=False
    'dwb.Save

End Sub

標籤：擅长 vba

上一篇：在excel中用VBA過濾

下一篇：將陣列粘貼到范圍上的索引符號