The runSecondStepofTool Algorithm:


  1. With Sheet2
    1. Concatenate each Record (Substituting a currency symbol of various NumberFormats where indicated)
    2. Assign the Record String to a Dictionary Object
    3. To the left of the Table insert a formula to concatenate Each Record + some additional strings

  2. With Sheet1
    1. Assign "Eur" to the Freight Currency Field of all Records
    2. Concatenate each Record (Substituting a currency symbol of various NumberFormats where indicated)
    3. Compare this Record String to the Sheet2 Dictionary. If found, Assign "V" to column "G" to the right of the Table
    4. In Column "H", If "V" not present in Column "G", assign an INDEX + MATCH formula using certain fields from the corresponding Row in Sheet2
    5. Replace Formulas in Column "H" with Values.
    6. Format Column "H"
    7. In Column "H", Replace "N/A" with "Not Requested"



To investigate look down ~ 900 rows in Column H to find clues as to problem. And when you get it to work a logic bomb will make it fail at the first "Not Requested" and all subsequent rows will be "Not Requested"



My Step 2 Algorithm
Sheet1 has no formatting except the headers
Run Step 1 Code on sheet2
Sort both sheets by Vendor then Plant Number then Part number


  1. With Sheet1 format Price and Freight NumberFormat with Currency Symbol to match sheet 2 Currency Symbol
  2. For each Record in Sheet 1 & 2
    1. If Vendor & Plant & Part# = Vendor & Plant & Part#, then Records Match
      1. Else Compare above and below Records to see which Sheet has missing/additional record
      2. Insert blank Highlighted row on sheet missing record. Adjust Row Counter as needed. GoTo Next Row (Record)

    2. If Freight = Freight Then Records Match Else Highlight Discrepancies
    3. If Price = Price Then Records Match Else Highlight Discrepancies
    4. If P.Currency.NumberFormat = Price.NumberFormatThen Records Match Else Highlight Discrepancies
    5. IF.Currency.NumberFormat = Freight.NumberFormat Then Records MatchElse Highlight Discrepancies

  3. Next Record (Row)
  4. With Sheets 1 & 2, delete all records with no highlighting for ease of investigation, keeping all records (rows) aligned


To investigate merely look from sheet to sheet, row by row, only rows with discrepancies are there.

With the clues above, can you see the Logic Bomb in your code?