vba - 在 Excel 中使用宏在循环中查找和替换

Question

我有两张纸。表 1 包含以下数据

表 1:

Column 1  column 2 
Hotel A   New York 
Hotel B   Melbourne 

我希望用这个值替换工作表 2 中的值

表 2 是这样的:

Column 1    Column 2   Column 3

Name        ....        .....
.....       ....        City 
....        ....        ....
Name        ....        .....
....        .....       City 

我理想的输出是:

Column1     Column 2    Column 3

Hotel A     ....        .....
.....       ....        New York 
....        ....        ....
Hotel B     ....        .....
....        ....        Melbourne  

因此，我希望在 sheet 1 中循环读取酒店的名称和城市，然后转到 sheet 2 并找到单词 Name 和 City 并将它们替换为我在 sheet 1 中读到的内容。我对 VBA 非常陌生，并且像这样开始了我的代码，它甚至进入了循环。为什么会这样呢？

Sub testLoopPaste()
   Dim j, k, L, b As String
   Dim i As Long
   Dim wb As Workbook
   Dim sht1 As Worksheet
   Dim sht2 As Worksheet

  Set wb = ThisWorkbook
  Set sht1 = wb.Sheets("Sheet1")
  Set sht2 = wb.Sheets("Sheet2")

   j = "Name"
   b = "City" 

   For i = 1 To 2

    k = sht1.Range("A" & i)
    L = sht1.Range("B" & i)

    sht2.Cells.Replace what:=j, replacement:=k, lookat:=xlWhole, MatchCase:=False
    sht2.Cells.Replace what:=b, replacement:=L, lookat:=xlWhole, MatchCase:=False

  Next i

 End Sub

如有任何提示或指导，我们将不胜感激。

Answer 1

Cells.Replace 将使用 Replacement 更改所有出现的 What。

您需要查找您要查找的单元格，然后替换该单元格中的值:

Sub testLoopPaste()
    'Note: existing code was declaring j, k and L as Variant
    Dim j As String, k As String, L As String, b As String
    Dim i As Long
    Dim wb As Workbook
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim found As Range

    Set wb = ThisWorkbook
    Set sht1 = wb.Sheets("Sheet1")
    Set sht2 = wb.Sheets("Sheet2")

    j = "Name"
    b = "City" 

    For i = 1 To 2
        ' always advisable to specify .Value rather than assuming it will be the default property    
        k = sht1.Range("A" & i).Value
        L = sht1.Range("B" & i).Value

        Set found = sht2.Cells.Find(What:=j, _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    After:=sht2.Cells(sht2.Rows.Count, sht2.Cells.Count), _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False, _
                                    SearchFormat:=False)
        If Not found Is Nothing Then
            found.Value = k
        End If

        Set found = sht2.Cells.Find(What:=b, _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    After:=sht2.Cells(sht2.Rows.Count, sht2.Cells.Count), _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False, _
                                    SearchFormat:=False)
        If Not found Is Nothing Then
            found.Value = L
        End If
    Next i

End Sub