Solved: Find and replace values from separate spreadsheet

[drewloveland]

Hello, thanks in advance for any help on this.

I'm using Excel 2003.

See the attached spreadsheet (this is only an example file for demonstration purposes.) I need a macro that will allow me to do lookups on that spreadsheet from a separate excel file (I'll post a 2nd example file below) such that it will replace any occurrences of the User ID with the actual person's name (for example, all occurrences of "ID12" would become "John Doe".) I would prefer to have the macro only open the IDs.xls spreadsheet when the macro is run (the location of the spreadsheet can remain static), but if necessary, I can have the IDs.xls open when Excel is started. I have too many IDs to replace to just do a simple find and replace. The spreadsheets I would run this on could have varying names, and the IDs could be on any number of tabs and in any column, but all occurrences on all tabs would need to be replaced.

Thanks and let me know if you need further clarification.

[drewloveland]

(I'll post a 2nd example file below)

See attached. This would be an example of a file where I might need to replace all the IDs in the spreadsheet with names.

[austenr]

Any reason you cant put the IDs and Names in the same workbook? If possible a simple VLOOKUP is what you seek.

[austenr]

or something like this. change the sheets, ranges and workbook names to yours:


With Workbooks("Book2.xls").Sheets(1)
tResult = Application.WorksheetFunction.VLookup( _
Sheets(1).Range("A" & tRow), .Range("A:D"), targetType, False)
End With

[drewloveland]

Unfortunately due to circumstances beyond my control, the spreadsheet containing the IDs/Names will always be separate. Basically I get excel dumps with lists that contain these IDs, but the application they get dumped from doesn't dump the names. So, I keep my own list separately of what name matches each ID. The finished product needs to have the actual names, thus my need for a macro to quickly replace them all. I could do something like this:


Cells.Replace What:="ID12", Replacement:="John Doe", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="IDA4", Replacement:="Joey Schmo", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


...and so on and so forth. But the actual list has nearly 100 ID/name entries, and I'd rather not have to "hard code" the IDs and names like that. Additionally, it would be nice to just be able to update the master spreadsheet when an ID/name combination gets added (the list gets updated frequently), rather than having to update the code.

[austenr]

This should do what you want although not a macro. You will have to alter the workbookname, rows, etc.

=VLOOKUP(B6,[productinfo.xls]Sheet1!$J$2:$L$15,3,False)
This would go in the workbook that gets downloaded with the ID's. You will have to copy this formula to the cells. Maybe someone will come along with a macro version for you.

Another question is will the ID's that are downloaded be scattered like they are in the example you provided? If so and you do not know what cells they will end up in, then the approach above will not work.

[drewloveland]

Another question is will the ID's that are downloaded be scattered like they are in the example you provided? If so and you do not know what cells they will end up in, then the approach above will not work.

Exactly, that's why I put them that way in the example--because the IDs are not always in the same column.

[austenr]

Do they have to stay where they are? The ID's that is? I'm assuming so.

[drewloveland]

Do they have to stay where they are? The ID's that is? I'm assuming so.

Yes.

[drewloveland]

Finally figured it out after looking at an old thread where another individual had a similar request to mine. Just had to add some syntax to make sure it selected all cells in all active worksheets before doing the replacements:


Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim wsTarget As Worksheet

Set wbTarget = ActiveWorkbook
Set wsTarget = ActiveSheet

Dim fnd As Variant
Dim myitems As Long
Dim REFsheet As Worksheet

Set wbSource = Workbooks.Open(Filename:="C:\XLMacros\IDs.xls", Origin:= _
xlWindows)

Set REFsheet = wbSource.Worksheets("IDMapping")
myitems = REFsheet.Range("A" & Rows.Count).End(xlUp).Offset(-1).Row
ReDim fnd(myitems, 1)

For myloop = LBound(fnd) To UBound(fnd)
fnd(myloop, 0) = REFsheet.Range("A" & myloop + 2)
fnd(myloop, 1) = REFsheet.Range("B" & myloop + 2)
Next myloop
wbTarget.Activate
Worksheets.Select
Cells.Select
For myloop = LBound(fnd) To UBound(fnd)
Selection.Replace what:=fnd(myloop, 0), replacement:=fnd(myloop, 1), lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next myloop
wbSource.Close

[austenr]

If your problem is solved, then please mark the thread solved. Thanks

[austenr]

If your problem is solved, then please mark the thread solved. Thanks