Viewing 15 posts - 1 through 15 (of 15 total)
  • Excel help (muiltiple find & replace)
  • SilentSparky
    Free Member

    So we’re changing over our Chart of Accounts shortly and I need to rebuild all of our reports.

    Rather than individually “finding and replacing” accounts over muiltiple worksheets, is there a simple bit of vba code that could do the job.

    I was thinking:
    – Add a worksheet into the workbook that needs updating with the old accounts listed in column A and the new mapped accounts in column B
    – Create some code that will look at this source worksheet column A and replace it with column B on all the other tabs.

    Is anyone a VBA guru that can shed some light on the code required?

    Ta

    CharlieMungus
    Free Member

    how many files do you need to do this to?

    TheSouthernYeti
    Free Member

    Set up a mapping doc with old and new in two columns.

    Then use this as a source for vlookups in other files.

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    djglover
    Free Member

    If you group worksheets find and replace works on all sheets so only one of each find and replace is needed.

    Stoner
    Free Member

    someone much better at VBA than me should be able to show you some code to run a routine using cells.replace on your workbooks and populated from an external source sheet

    soma_rich
    Free Member

    I think the Southern Yetis idea would work. VBA could do it. I dont think I understand what your asking enough to actually write something for you though.

    Stoner
    Free Member

    rich – two columns. First with a list of worksheet names in, second with a list of replacement worksheet names.

    I imagine you could write a routine that for the list in A sequentially cells.replace every occurrence of the value in a workbook with the corresponding entry in col B in a given workbook.

    mrmo
    Free Member

    let me get this right, you have a customer list and you are changing the names of each customer.

    may not be customers but that is how i see it.

    best approach i can think of

    use a for next loop, size is determined by number of customers on new list.

    create a variable this is old name, create a second varible2 this is the first variable offset one cell to pick up the new name.

    start at row 1 on the book you want to change

    if worksheets.cell()=variable then worksheets.cell()=variable 2
    move to next row.

    this would be repeated through the whole set of records.
    You then go back to the customer match up table and pick the second name. repeat….

    If you want me to have a look at the coding and actually get the syntax correct i can have a look a bit later.

    TheSouthernYeti
    Free Member

    silentsparky – my method will definitely work.
    If you have hundreds of sheets then I’d consider VBA other wise…

    Create your mapping doc.
    Write your vlookup.
    Paste into sheet.
    Copy down.
    Copy new codes over old.

    I assume all your reporting files are in a consistant format. Should be easy.

    CharlieMungus
    Free Member

    try this

    Start a new file, in column A, list the names of the files you want to act on, including the .xls extension

    In column b, list the current name, with the corresponding replacement in column c.

    Run this macro

    Sub Macro1()

    ‘ Macro1 Macro
    ‘ Macro recorded 13/01/2011 by User


    Dim pip(100) As String
    Dim pop(100) As String
    Dim pap(100) As String
    Dim counter As Integer
    Dim filecounter As Integer

    For filecounter = 1 To 2
    pip(filecounter) = Cells(filecounter, 1).Value
    For counter = 1 To 3

    pop(counter) = Cells(counter, 2).Value
    pap(counter) = Cells(counter, 3).Value

    Next counter

    Workbooks.Open Filename:=pip(filecounter)

    Cells.Select

    For counter = 1 To 3
    Selection.Replace What:=pop(counter), Replacement:=pap(counter), LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Next counter
    ActiveWorkbook.Save
    ActiveWorkbook.Close

    Next filecounter
    End Sub

    this assumes all files are in the same folder, including the fiel with the references and macro. It also assume you are only using one sheet and that the files open on that sheet

    mrmo
    Free Member

    actually just a quick thought, unless you have lots of worksheets do this, create new workbook with your comparisons. go to data workbook, insert column after name your changing. in this add vlookup() drag this to bottom of column. now copy, paste as value on top of the original name then delete the column you created and filled with vlookups.

    SilentSparky
    Free Member

    Right to answer a few questions;

    Need to do this to various files/packs probably 15 in total. Each pack has muiltiple tabs (upto 20) and mpst tabs and a few hundred lines long.

    The Southern Yeti – that would work for my reports as I tend to link my formula retrieve to a particular column (although still not too quick to update because of the volume). But I’m sure I’ll be asked to update other peoples pack/reports which maynot be in an easy to update format ie. having the account code that needs changing enbedded within a formula in a random column.

    CharlieMungus
    Free Member

    what are the names of the tabs?

    an example ?

    does each file have the same names on the tabs?

    SilentSparky
    Free Member

    Example of tab names:
    “UK”, “Enterprise”, “Consumer”, “Voice”, Data”

    Not each file has the same tabs, but I guess I could tweak the code for each workbook?

    CharlieMungus
    Free Member

    You could tweak the code, or if you have a list of the tabs for each workbook, we can include it in the macro

Viewing 15 posts - 1 through 15 (of 15 total)

The topic ‘Excel help (muiltiple find & replace)’ is closed to new replies.