Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to do a loop within a loop within a loop as follows:
    I want to compare two files and highlight the new rows and cells in the latest file, as follows:
    On the enclosed spreadsheet, I click on a button called 'Compare' which then asks me which file I want to compare it to. The other file is opened.
    My current file is stored under the variable FNAME. The old file hs the variable ONAME
    So far so good, I can do this. It's the testing that is bamboozling me.
    I want the code to do the following, explained in English (!)

    The list in each worksheet are not necessarily the same length

    Grab the PO number from FNAME and check if it exists in the same column in ONAME
    If it does not exist in ONAME then highlight the row in FNAME
    If it does exist in ONAME, then compare column B in FNAME with column B in ONAME. If they are different, highlight column B in FNAME. Do this for each column up to and including column K
    Check the next PO in FNAME

    So how can I accomplish this?
    Thanks in advance!
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    There is no code in that workbook so it's difficult to add the necessary testing to it!
    Basically you want to loop through column A, use the Find method on column A on the other workbook. If it returns Nothing, then highlight the row in this workbook; if it returns a range, then you can use the range's row number and then loop through each column, highlighting as applicable.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Apologies - I have included the code in the attached file - the first thing it does is open the file to compare and I made a start on the code but figured there must be a better method than the one I was attempting.
    Your advice would be most welcome.
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK I've nearly got it but even though the code seems to me to be logically correct it sometimes returns the wrong results and never seems to highlight the entire row.
    The idea is this - the user selects a file (stored under the variable SAVIE) to be compared to the main file (FNAME).
    If the PO number (column A) is not found in the SAVIE file then the entire row in FNAME has its text coloured red.
    If the PO number is found in the SAVIE file, then the code checks each cell in that row from A to K (11 columns) and if any item differs from the FNAME file then the appropriate cell in FNAME has its text coloured red.
    So the code has to loop through the PO items in FNAME, loop through the same column in SAVIE and loop through columns A to K in SAVIE. Both files have exactly the same layout.
    The code I am using for the loops is as follows, and the actual file with the full code (MAIN 240410.xls), along with a dummy compare file (Copy 220410.xls), is enclosed.
    If anyone can point out where my code is flawed I would be most grateful!
    Thanks for looking:


    'now loop through old file
    Windows(savie).Activate
    alngMaxRow = Range("a65536").End(xlUp).Row
    For alngrow = alngMaxRow To 2 Step -1 'Only go up to row 2
    If (Cells(alngrow, 1).Value = newpo) Then
    findy = 1
    colnum = 2
    Do
    oldpo = Cells(alngrow, colnum).Value
    Windows(fname).Activate 'go back to new asean file
    If Cells(lngrow, colnum).Value = oldpo Then
    GoTo zapo
    End If
    Cells(alngrow, colnum).Select
    Selection.Font.ColorIndex = 3


    zapo:
    Windows(savie).Activate

    colnum = colnum + 1
    Loop Until colnum = 12 'Do 11 columns, up to column K
    'End If
    End If
    Next alngrow
    Windows(fname).Activate
    If findy = 0 Then 'match wasn't found
    ActiveCell.EntireRow.Select
    Selection.Font.ColorIndex = 3
    End If
    Windows(fname).Activate

    findy = 0
    Next lngrow

    Windows(savie).Close 0
    Attached Files Attached Files

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ted-OZ,

    I took a look at your code and would like to offer a couple of suggestions that may help you debug your problem.

    1. Add "Option Explicit" to the top of each module, this will tell you if you use a variable that is not DIMed.

    2. Several of your multiple DIM statements don't have a type at the end. These will default to Variant which is inefficient and may lead to some problems. I find it best to limit dims to one variable per line which allows you to add a comment to the end for clarity.

    3. Indent your code so you can see the structure of the logic and tag your end statements with a comment telling you what they end.
    Code:
    If iAns = vbYes Then
      Your code lines here indented 2 spaces
    Else
      Your Else code indented 2 spaces
    End if   'iAns= vbYes
    Note: indent deeper for imbedded structures, i.e. an IF within another IF etc.

    4. Tag your variable names with their types, e.g. Integers as iAns, Longs as lCnt, Strings as zPath, etc., doesn't matter what you use as long as you are consistent.

    Neatly written and heavily commented code is usually much easier to debug not only by the writer {when he goes back to it 6 months later} but also by anyone who has to look at the code.

    I also noticed that the ranges in your SORTS are hard coded, e.g. Range("A1:K29").Sort Key1:=Range("A2")), and the sheet actually has 35 or so rows. If you code the range with a single reference, e.g. Range("A2").Sort Key1:=Range("A2") Excel will sort the entire contiguous area and you don't have to worry about the size of the data table.

    I hope this is helpful.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the tips, RG - as a self taught coder, there are many things I can learn!

    Just about got it now, have replaced

    ActiveCell.EntireRow.Select

    with

    Rows(lngrow).EntireRow.Select

    This solved my issue of not selecting the correct row to colour red if the PO wasn't found. The active cell remains the same hence only 1 row was ever coloured. This is now fixed for anyone who is reading the code and can learn from the technique.

    I still get very rare anomolies - especially if the PO code appears twice.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can make your code more efficient by NOT selecting

    Instead of something like:
    Cells(alngrow, colnum).Select
    Selection.Font.ColorIndex = 3

    Use:
    Cells(alngrow, colnum).Font.ColorIndex = 3


    Steve

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ted,

    You're welcome.

    Another thing you might look at is eliminating .Selects where you're just doing one operation and then moving on, e.g.
    Code:
    Cells(alngrow, colnum).Select
    Selection.Font.ColorIndex = 3
    Can be replaced by:
    Code:
    Cells(alngrow, colnum).Font.ColorIndex = 3
    This style of coding makes less to read and for large files increases operating speed of the macro. As a self taught coder you're probably learning a lot by recording short macros and then incorporating them into your code. I know, also as a self taught VBA'er - although I had formal education in other languages, I learned a lot this way. Unfortunately, the macro recorder isn't very smart so you need to manually optimize the code where possible.

    A couple of books you might want to consider:
    Writing Excel Macros by: Steven Roman - O'Reily ISBN:1-56592-587-4
    Special Edition Using Excel Visual Basic for Applications by: Jeff Web - Que ISBN: 0-7897-0269-X
    These are 2 of my favorites, I have quite a collection of books on the subject. I've found that most of the content in different books is pretty much the same but there is always about 5% that is different from the others {author's slant and or practical knowledge} that makes it worth having the book. When you consider what your time is worth $40 for another book where that 5% can save you hours of time is well worth it to me.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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