Find and replace in a loop using macro in Excel

Question

I have two sheets. Sheet 1 contains the following data

Sheet 1:

Column 1  column 2 
Hotel A   New York 
Hotel B   Melbourne

And I wish to replace the value in sheet 2 with this value

Sheet 2 is like that :

Column 1    Column 2   Column 3

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

My ideal output will be :

Column1     Column 2    Column 3

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

So, I wish to go through a loop in sheet 1 and read the name and city of hotels and go to sheet 2 and find the words Name and City and replace them with what I read in sheet 1. I'm very new in VBA and started my code like that and it even goes to loop. Why is it so?

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

Any tips or guidance is appreciated.

How would you known when `Name` is `Hotel A` or `Hotel B` ?? — Robin Mackenzie, Mar 29 '17 at 05:14
@RobinMackenzie The first `Name` in `sheet 2` is `hotel A` because it is the first row in `Sheet 1` and the second `Name` is `hotel B` because it is the second row in `sheet 1`. In other words, The number of `Name` in `Sheet 2` is equal to the number of rows in `sheet 1`. — MFR, Mar 29 '17 at 05:18

YowE3K · Accepted Answer · 2017-03-29T07:16:39.813

Cells.Replace will change all occurrences of What with Replacement.

You need to Find the cell you are looking for, and then replace the value in just that cell:

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

I tested the code. Hotel A and Hotel B end up in reversed positions. The cities are correct. — June7, Mar 29 '17 at 07:06
@June7 - I was worried about that - I was hoping that you would have headings in the first row and therefore the first match wouldn't be in cell A1. Give me a few minutes and I will update the answer to start the search at Cells(Rows.Count, Columns.Count). — YowE3K, Mar 29 '17 at 07:14
@June7 - OK - I have forced the `Find` to start from the very last cell on the sheet. That way, if the first match is in cell A1, it will still find it. — YowE3K, Mar 29 '17 at 07:17

Amorpheuses · Answer 2 · 2017-03-29T07:06:46.523

This should work. The loop searches for each Name and City in sht1 and replaces the first occurrence in columns "A" and "C" of sht2:

Sub testLoopPaste()
  Dim i As Long
  Dim wb As Workbook
  Dim sht1 As Worksheet, sht2 As Worksheet

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

  Dim Loc As Range
  For i = 1 To sht1.Range("A" & sht1.Rows.Count).End(xlUp).row
   Set Loc = sht2.Columns(1).Find(What:="Name")
   If Not Loc Is Nothing Then Loc.Value = sht1.Cells(i, "A")
   Set Loc = sht2.Columns(3).Find(What:="City")
   If Not Loc Is Nothing Then Loc.Value = sht1.Cells(i, "B")
  Next i

End Sub

Find and replace in a loop using macro in Excel

2 Answers2

Linked