+ Reply to Thread
Results 1 to 10 of 10

Find/Replace macro with lookup table of change from to change to

  1. #1
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Find/Replace macro with lookup table of change from to change to

    Hi All,

    I'm having an annoying issue when running reports from our financial system. Some account/analysis codes which are 3 or 4 character text are reformatted to numbers or dates when exported from the system to excel.

    Example:
    Analysis code 6E2 is reformatted to the number 600 and Account code JAN5 is reformatted as date 36526 or 01/01/2000

    I basically have to find and replace all the account/analysis errors manually by filtering the data set.

    I've attached an example of an export with errors appearing in columns E & H only and highlighted the errors in yellow.

    36526 or 01/01/2000 change to JAN0
    37257 or 01/01/2002 change to JAN2
    38353 or 01/01/2005 change to JAN5
    38718 or 01/01/2006 change to JAN6
    39083 or 01/01/2007 change to JAN7
    39448 or 01/01/2008 change to JAN8
    6 or 6.00E+00 change to 6E0
    600 or 6.00E+02 change to 6E2
    60000 or 6.00E+04 change to 6E4
    7 or 7.00E+00 change to 7E0
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Find/Replace macro with lookup table of change from to change to

    Setup to work for your sample file:
    Please Login or Register  to view this content.
    Note you can generally prefix stuff with an apostrophe to format as text.

  3. #3
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Find/Replace macro with lookup table of change from to change to

    Hi
    Based on the information you provided in your post it appears that when you export data from your fiscal system and import it into Excel you are importing "Values", which is why Excel is trying to evaluate those values and treats them accordingly. Do you have the option to import/paste as "text"? Without seeing a sample of your exported data to test I cant suggest much more at this stage.
    Tony

  4. #4
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: Find/Replace macro with lookup table of change from to change to

    That works perfectly yudlugar thank you. Trying to understand the code and adapt it to my actual situation, am i reading it correctly in thinking Count1 = range of the lookup table and count2 = range of the dataset? how would i adapt this if the lookup table was in another tab?

    ARGK the export function is just a simple export to excel function with no option to formatting so guessing it uses standard file formatting. Example of exported data is within the test file attached in first post if you have ability to view

  5. #5
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Find/Replace macro with lookup table of change from to change to

    You are correct, count1 is the rows of the lookup table and count2 is the rows of the dataset, the columns are given as ABCE andH throughout the code. I've tried to add a bit more commenting to allow you to adapt it:
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: Find/Replace macro with lookup table of change from to change to

    great i can follow that perfectly. Last question, the lookup table is in a "procedures notes" tab in a specific range (J5:L15) i.e. there is other data contained in rows 5 to 15
    Last edited by Gti182; 09-19-2013 at 07:19 AM.

  7. #7
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Find/Replace macro with lookup table of change from to change to

    in my example I used columns A and B as the "text to be replaced" and C as the "replacement text". Therefore you would need to change A and B to J and K and C to L.

  8. #8
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: Find/Replace macro with lookup table of change from to change to

    ah perfect, sorry should have picked that up. Code works lovely!

  9. #9
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: Find/Replace macro with lookup table of change from to change to

    amended code runs flawlessly but is quite slow +- 10 seconds on just under 2000 rows of data. This will increase to over 20000 rows by yearend. Is there a way to speed up the code in any way? My actual code below

    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Find/Replace macro with lookup table of change from to change to

    in the example you gave me the top half of the lookup table applied to one column of the dataset and the bottom half applied to another column. You could try changing it so you run two loops and only apply them to the relevant columns?

    It would probably be quicker to run a find and replace type method but the issue with this was that I couldn't figure out how to do it with find and replace due to looking for "6" and there being lots of 6's it could find. It therefore gave incorrect answers.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Basic Find, Replace and Format change macro
    By cmphibbs in forum Excel General
    Replies: 1
    Last Post: 06-27-2013, 06:22 AM
  2. [SOLVED] Need a macro to find and replace plus change format of another colum
    By cejamgr in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-29-2013, 07:13 PM
  3. Macro find and change value according to table
    By dudumomo38440 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-03-2011, 04:00 AM
  4. how to change the code to do find the value and replace value
    By jay11 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-11-2010, 09:33 PM
  5. Change Sheet Reference w/o Find/Replace
    By Phillycheese5 in forum Excel General
    Replies: 1
    Last Post: 05-17-2007, 06:00 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1