Looking for an advanced find and replace

[Lavster]

Hi I wondered if someone can give me an idea. I've got lots of vb programming experience but not much vba

basically I have a list of values

then I want to have another sheet with the list with that list on column a and what I want to replace it with in b

so example
sheet 1 a1 = 9999
then find 9999 in sheet 2 in Column a125 but replace the sheet 1 value with b125

 

[bschwartz]

Well it won't replace the value in A1, but a simple VLOOKUP will return the value in B125. For instance:

=VLOOKUP(A1,Sheet2!$A$1:$B$999,2,0)

Not sure if this helps...

 

[hiker95]

Lavster,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Sample raw data worksheets:

Excel 2007
	A	B
1	1111	11
2	2222	22
3	3333	33
4	9999	99
5
Sheet2

Excel 2007
	A
1	9999
2	3333
3	2222
4	1111
5
Sheet1

After the macro in worksheet Sheet1:

Excel 2007
	A
1	99
2	33
3	22
4	11
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub Find_Replace()
' hiker95, 09/08/2014, ME804146
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, f As Range
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
With w1
  For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    Set f = w2.Columns(1).Find(c.Value, LookAt:=xlWhole)
    If Not f Is Nothing Then
      c = w2.Cells(f.Row, 2).Value
      Set f = Nothing
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Find_Replace macro.