Solved How do I find and replace via macro

September 22, 2019 at 10:44:30
Specs: Windows 10
Hi

I have a excel workbook that I need to find and replace data in sheet 1 with data from sheet 2.

On sheet 1 in columns A & D I have specific data that matches with data in column A of sheet 2. In column B of sheet 2 on same row there is a detailed explanation.

Im looking to go through sheet 1 and look for specific code and then compare with sheet 2 and replace with the data in column B of sheet 2 on sheet 1. There will be numerous duplicated entries in sheet 1 and I need them to go all the way through.


There are 80 different codes that could be replaced.

Any Ideas?


See More: How do I find and replace via macro

Reply ↓  Report •

✔ Best Answer
September 24, 2019 at 08:12:55
Try the following code.

I added a message box to warn the user if a piece of data on Sheet 1 is not found on Sheet2. This basically prevents the code from crashing when it tries to Copy something that it didn't find. There are many ways to handle that situation, including suppressing the error, but I figured I'd at least warn the user.

Sub Find_Replace()
Dim col As Long
Dim lastRw As Long, nxtRw As Long
Dim d As Range

Application.ScreenUpdating = False

'Loop through Sheet1 Column A then Column D
  With Sheets(1)
    For col = 1 To 4 Step 3
  
'Find last row with data
     lastRw = .Cells(Rows.Count, col).End(xlUp).Row
   
'Find & Replace, Warn id Data not found

      For nxtRw = 2 To lastRw
        Set d = Sheets(2).Columns(1).Find(.Cells(nxtRw, col), lookat:=xlWhole)
            If d Is Nothing Then
             Application.ScreenUpdating = True
               MsgBox .Cells(nxtRw, col) & " Not Found on Sheet2"
             Application.ScreenUpdating = False
               GoTo FindNxt
            End If
          Sheets(2).Cells(d.Row, 2).Copy .Cells(nxtRw, col)
FindNxt:
      Next
    Next
  End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
September 22, 2019 at 17:38:01
Let me see if I understand.

First, when you use the word "code" in your 3rd paragraph, you are referring to the "data" that you mentioned in paragraphs 1 & 2, correct?

Second, your Sheets look something like this:

Sheet1:

        A         B         C         D
1     Data1                         Data2
2     Data3                         Data4


Sheet2: (Sorted or not sorted?)

        A        B
1     Data1     Exp1                    
2     Data2     Exp2
3     Data3     Exp3                   
4     Data4     Exp4


When the macro is done, you want Sheet1 to look like this:

        A         B         C         D
1      Exp1                          Exp2
2      Exp3                          Exp4

I don't get this:

"There will be numerous duplicated entries in sheet 1 and I need them to go all the way through."

Please explain that in more detail. What do mean by "I need them to go all the way through"?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
September 23, 2019 at 08:33:15
Hi. I think we are 99% there.

Looking at your sheet layouts sheet 2 where all the data is that will not be sorted .

Sheet 3 is sort of yes that’s what I need but it could be that on column A. data 1 is in numerous cells so I need it to search all the way down the workbook for data1 and then replace with exp 1 and it could also be in column D.

Best way of describing is for it to look all the way down columns A & D for let’s say data 1 first and replace with exp 1 and then carry on doing the same thing looking all the way down A&D replacing data 2 with exp 2 and so on until all cells changed in columns that have data in them. Does that make sense? If not I can try and add a file to show


Reply ↓  Report •

#3
September 24, 2019 at 03:41:12
re: "Sheet 3 is sort of yes"

Where did Sheet3 come from? There is no mention of Sheet3 in your first post.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
September 24, 2019 at 04:19:51
Sorry sheet 3 as per what you posted on here. Not a sheet 3 in workbook . That was my fault and tired. What o meant was what it should look like as in what you posted on here

message edited by GDB


Reply ↓  Report •

#5
September 24, 2019 at 08:12:55
✔ Best Answer
Try the following code.

I added a message box to warn the user if a piece of data on Sheet 1 is not found on Sheet2. This basically prevents the code from crashing when it tries to Copy something that it didn't find. There are many ways to handle that situation, including suppressing the error, but I figured I'd at least warn the user.

Sub Find_Replace()
Dim col As Long
Dim lastRw As Long, nxtRw As Long
Dim d As Range

Application.ScreenUpdating = False

'Loop through Sheet1 Column A then Column D
  With Sheets(1)
    For col = 1 To 4 Step 3
  
'Find last row with data
     lastRw = .Cells(Rows.Count, col).End(xlUp).Row
   
'Find & Replace, Warn id Data not found

      For nxtRw = 2 To lastRw
        Set d = Sheets(2).Columns(1).Find(.Cells(nxtRw, col), lookat:=xlWhole)
            If d Is Nothing Then
             Application.ScreenUpdating = True
               MsgBox .Cells(nxtRw, col) & " Not Found on Sheet2"
             Application.ScreenUpdating = False
               GoTo FindNxt
            End If
          Sheets(2).Cells(d.Row, 2).Copy .Cells(nxtRw, col)
FindNxt:
      Next
    Next
  End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
September 24, 2019 at 13:09:14
Derbydad you are a legend. Completely correct. Many thanks

Reply ↓  Report •

Ask Question