Find and Replace Based on a Table

[farles44]

Hi Everyone,

This is my first post. I am not great with excel (I mostly accomplish what I want via record macro). I have no idea how to start to write a VBA or Macro, but I have a request. Can someone help me get started on writing what I assume is a very simple VBA. If you happen to know how to write this, can you also help me with uploading into excel?

Ideally what I need to do is this:

Have a 2 sheet workbook. Sheet 1 would be a place users could past in their data. This data has a column that has information that isn't readable but corresponds to a key.

For example, the data pasted into sheet 1 might be a bunch of transactions (Any column or row), and the transactions are identified with

NA-24m
BC-23m
AD-55d



On Sheet 2 in the workbook, there is a key (A1:B335) that has the corresponding info:

NA-24M = Return Transaction
BC-23m = Discharge Transaction


I have used V-lookup for myself to easily identify what the codes refer to, but I want to create an excel document that has a macro build into a button that other users in my department can press to essentially find and replace all the codes with the corresponding transaction type based on the key table on sheet 2. Lastly, the sheet names could potentially change names...not sure if that is pertinent.

Any help would be appreciated!

 

[L. Howard]

So if there is NA-24M in cell J33 on sheet 1, the macro would find NA-24M on sheet 2 in column A and replace (overwrite) the NA-24M (in J33) with the value adjacent in column B with Return Transaction.

And these values like NA-24M are in a cell by themselves or are they part of a string like "The red fox uses NA-24M fur conditioner"?

You mention these values can be anywhere and everywhere. What else is on sheet 1, and how would I find all these values, that is, how would I know to look at J33 to see or find that value there?

Howard

 

[farles44]

/So if there is NA-24M in cell J33 on sheet 1, the macro would find NA-24M on sheet 2 in column A and replace (overwrite) the NA-24M (in J33) with the value adjacent in column B with Return Transaction.

Yes! That is exactly what I need.


And these values like NA-24M are in a cell by themselves or are they part of a string like "The red fox uses NA-24M fur conditioner"?

They are in a cell by themselves. They will likely be in the same column every time as well, but its possible it could change. Depending on the exported report.

You mention these values can be anywhere and everywhere. What else is on sheet 1, and how would I find all these values, that is, how would I know to look at J33 to see or find that value there?

There are various reports that could generate from the program I am getting the data out of, so the size and variation of sheet 1 will change. Essentially if I could do a record macro of me "finding and replacing" sheet 1 with the 335 possibilities on sheet 2, that would accomplish the same thing. Is there a way to make a VBA search all active cells in Sheet 1 for the terms in Column A on Sheet 2, and replace any finds with the corresponding terms in Columb B of sheet 2?

Thanks for your help!

 

[L. Howard]

Okay, I got the set up. I'll see what I can do.

Howard

 

[farles44]

Okay, I got the set up. I'll see what I can do.

Howard

Awesome! You rock!

 

[L. Howard]

This should work for you.

To install this in your workbook do this:

Say you are on sheet 1... hit Alt + f11 (this will take into and out of the VB Editor. Sheet to VB and VB to sheet)

In the VB Editor > look up and to the left of the page in the menu bar for the word Insert > click on it > look for Module in the drop down and click on it.

This will give you a Standard Module, which will be a large white space to the right, and will probably be named Module1 as seen in the Project Explorer to the left and in the Project Tree below all the sheet names.

Copy and paste this code in that module. Notice if you already have Option Explicit at the top of the page. If you have two, then delete one.

Now Alt + f11 back to the worksheet.

You can run the macro using a button, or a Shape, installed on the sheet with the macro assigned to it or hit Alt + f8 and see a Macro Name Box and in the box will be the names of the macros you have in the workbook, you will probably have just the one. Click on the name the click Run.

I have used Sheet1 and Sheet2 as the sheet names, if your sheet names are different you will need to change them in the code...

From With Sheets("Sheet2") to With Sheets("My Sheet Name") as an example.

Let me know how it goes for you.

Howard

Option Explicit

Sub Replace_Overwrite()
Dim LRow As Long, i As Long
Dim varSearch As Variant

With Sheets("Sheet2")
    LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    varSearch = .Range("A2:B" & LRow)
End With

With Sheets("Sheet1").UsedRange
    For i = LBound(varSearch) To UBound(varSearch)
        .Replace what:=varSearch(i, 1), replacement:=varSearch(i, 2), lookat:=xlWhole
    Next
End With
    
End Sub

 

[farles44]

This should work for you.

To install this in your workbook do this:

Say you are on sheet 1... hit Alt + f11 (this will take into and out of the VB Editor. Sheet to VB and VB to sheet)

In the VB Editor > look up and to the left of the page in the menu bar for the word Insert > click on it > look for Module in the drop down and click on it.

This will give you a Standard Module, which will be a large white space to the right, and will probably be named Module1 as seen in the Project Explorer to the left and in the Project Tree below all the sheet names.

Copy and paste this code in that module. Notice if you already have Option Explicit at the top of the page. If you have two, then delete one.

Now Alt + f11 back to the worksheet.

You can run the macro using a button, or a Shape, installed on the sheet with the macro assigned to it or hit Alt + f8 and see a Macro Name Box and in the box will be the names of the macros you have in the workbook, you will probably have just the one. Click on the name the click Run.

I have used Sheet1 and Sheet2 as the sheet names, if your sheet names are different you will need to change them in the code...

From With Sheets("Sheet2") to With Sheets("My Sheet Name") as an example.

Let me know how it goes for you.

Howard

Option Explicit

Sub Replace_Overwrite()
Dim LRow As Long, i As Long
Dim varSearch As Variant

With Sheets("Sheet2")
    LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    varSearch = .Range("A2:B" & LRow)
End With

With Sheets("Sheet1").UsedRange
    For i = LBound(varSearch) To UBound(varSearch)
        .Replace what:=varSearch(i, 1), replacement:=varSearch(i, 2), lookat:=xlWhole
    Next
End With
    
End Sub

You are a scholar. This is perfect. Do you take free-will donations?

 

[L. Howard]

No, no donations thank you. Glad it worked for you.

Just to add, should you a need to FIND the same data, and instead of overwrite it, add some text next to it, or your initials... whatever and you need to retain the values as you found them, try this with the same data.

Sub Replace_Add_String()

Dim LRow As Long, i As Long
Dim varSearch As Variant
Dim c As Range

With Sheets("Sheet2")
    LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    varSearch = .Range("A2:B" & LRow)
End With

With Sheets("Sheet1").UsedRange

   For i = LBound(varSearch) To UBound(varSearch)

        Set c = .Find(varSearch(i, 1), lookat:=xlWhole)

        If Not c Is Nothing Then
            'c = varSearch(i, 2)
            c.Offset(, 1) = varSearch(i, 2)
            c.Offset(, 2) = "lhk"
        End If

   Next

End With
    
End Sub

So, with this part of the code...

If Not c Is Nothing Then
'c = varSearch(i, 2) < - This line will overwrite as previous code. (it is commented out using the ' in front of c )
c.Offset(, 1) = varSearch(i, 2) < - This line will put the column B values one column to the right if the found value.
c.Offset(, 2) = "lhk" < - This line puts "lhk" tow columns to the right of the found value.
End If

For what its worth.

Howard

 

[farles44]

Hi Howard-

Quick update, would it be possible to make it so this macro ran on only what is selected in sheet 1? I really want to be able to highlight a column and then run this on that particular column. Is that possible/hard to tweak?

Thanks,

Matt

 

[L. Howard]

Try either of these two macros. For both, first run the code and see an Input Box, follow the prompt.

The first requires the column number entered into an Input box, i.e. 1 to 16384 (or less depending on Excel version)

The second code is very versatile, 1) Click on a column header for that column or many columns using Ctrl key 2) Select a range of cells with the mouse, 3) Use Ctrl key and select individual cells all over the sheet, 4) Click on the row headers for any number of rows, also using the Ctrl key.

Howard

Sub Replace_Overwrite_Col_Number()
Dim LRow As Long, i As Long
Dim varSearch As Variant
Dim xCol As Long

xCol = InputBox("Enter Column Number to search.")

With Sheets("Sheet2")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varSearch = .Range("A2:B" & LRow)
End With

With Sheets("Sheet1").Columns(xCol)
For i = LBound(varSearch) To UBound(varSearch)
.Replace what:=varSearch(i, 1), replacement:=varSearch(i, 2), lookat:=xlWhole
Next

End With

End Sub





Sub Replace_Overwrite_Express()

Dim rRange As Range
Dim LRow As Long, i As Long
Dim varSearch As Variant
Dim xCol As Long

On Error Resume Next

Set rRange = Application.InputBox("With you mouse, select a range of cells to evaluate.", _
"RANGE COLLECTOR", , , , , , 8)


With Sheets("Sheet2")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varSearch = .Range("A2:B" & LRow)
End With

With rRange
For i = LBound(varSearch) To UBound(varSearch)
.Replace what:=varSearch(i, 1), replacement:=varSearch(i, 2), lookat:=xlWhole
Next
End With

End Sub