Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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?

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    OK, answers [img]/forums/images/smilies/biggrin.gif[/img]

    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 [img]/forums/images/smilies/sad.gif[/img]

    Thanks

    Ian

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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?

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •