Automated find and replace, array to array (Win 2k, Office 2k)

Topic

New Reply

Dear All

We are using some spreadsheets to prep data for loading onto a server, during the preparation work I’ve managed to persuade folks to load less data, this has resulted in me now needing to be able to replace values in a set of comma separated strings with values from a table, where column A in the table matches what’s in the string and cloumn B is the new value.

I reckon it’ll take me about 5 hours to do this manually across the 5 spreadsheets already prepared, is there a faster way of doing this with some code?

It would be nice if there was a way of deleting values from the string if there was no match in the table of values.

Thanks in advance

Ian

Reply | Quote

Replies

It is possible to find and replace using code, but we’d need to know some more details.
Do you want to do this across all cells in a worksheet, or in a specific range?
Do you want to do this across all worksheets in a workbook, or in specific ones?
Do you want to do this across multiple workbooks?

Reply | Quote

OK, answers

All cells in specific range, basically I’ve got a column in the spreadsheet with these CSV strings in.

There’s only worksheet in each of the workbooks I’m trying to sort out.

There are five spreadsheets I’d like to use the code in.

I’ve been trying to work out something with the macro recorder, that usually gets me pointed in the right direction, but with this challenge it’s just confuding me even more

Thanks

Ian

Reply | Quote

One more question: is each word/phrase to be replaced a comma-separated entry, or do you want to replace parts of the comma-separated entries? For example, if you have

one egg,two biscuits,three apples

is it OK to replace “two” with “five” (if that occurs in the list), or should only “two biscuits” as a whole be replaced?

Reply | Quote

The CSV values are all 2 digit alpha numeric values, for exampe the string starts (400 values in some, so only a few here) 00,01, 02, 03, 04 etc

I’d want to replace the complete, but then only have 00 = A1 type entries in the two columns of my table

Thanks

Ian

Reply | Quote

You can use code like this. Replace the file names in the ProcessAll macro with the appropriate paths/names.
I assumed that the table is in columns A and B in the workbook containing the code, and that the CSV values to be processed are in column A. Adjust the code if necessary.

Sub ProcessAll()
ProcessOne “C:TestWorkbook1.xls”
ProcessOne “C:TestWorkbook2.xls”
ProcessOne “C:TestWorkbook3.xls”
ProcessOne “C:TestWorkbook4.xls”
ProcessOne “C:TestWorkbook5.xls”
End Sub

Sub ProcessOne(strFile As String)
Dim wbkCur As Workbook
Dim wshCur As Worksheet
Dim wbkOth As Workbook
Dim wshOth As Worksheet
Dim r As Long
Dim m As Long
Dim strFind As String
Dim strRepl As String

On Error GoTo ErrHandler

‘ Workbook with the table and code
Set wbkCur = ThisWorkbook
Set wshCur = wbkCur.Worksheets(1)
‘ Last row in table
m = wshCur.Range(“A65536”).End(xlUp).Row
‘ Open other workbook
Set wbkOth = Workbooks.Open(Filename:=strFile)
Set wshOth = wbkOth.Worksheets(1)
‘ Loop through the table rows
For r = 1 To m
‘ Find and replace strings
strFind = wshCur.Cells(r, 1)
strRepl = wshCur.Cells(r, 2)
‘ Execute the find/replace
wshOth.Range(“A:A”).Replace What:=strFind, Replacement:=strRepl, LookAt:=xlPart
Next r
‘ Close and save workbook
wbkOth.Close SaveChanges:=True

ExitHandler:
‘ Clean up
Set wshOth = Nothing
Set wbkOth = Nothing
Set wshCur = Nothing
Set wbkCur = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote