Quote:
Originally Posted by gmaxey
Batman,
Just headed out for a short leisure trip. Haven't fully digested your comments and I will have to look it over closely but I think you are correct about 2. Thank you.
|
We assume that
1. REF_SHEET has 1000 rows - the first 999 rows contain e.g. "DemoData_A000001.zip", "DemoData_A000002.zip", ..., "DemoData_A000999.zip" - the last row contains "DemoData_0000015_Import.zip"
2. NDS_SHEET has 10 rows, each containing "DemoData_0000015_Import.zip" Therefore, there is only 1 result: <REF/Contol# from the last row of REF>< 8 columns from the first row of NDS>
A. from DIC - 10 FOR loops to add key = "DemoData_0000015_Import.zip", item = 1 - 999 FOR loops and the code will not find "DemoData_0000015_Import.zip" (the only key with DIC). Only loop 1000 will find "DemoData_0000015_Import.zip" on the last line of REF. In total INSTR is executed 1000 times + 10 times "If Not dic.Exists(filename) Then dic.Add filename, and"
B. Without DIC For lngIndex = 1 to 999 and lngItemIndex = 1 to 10 the code executes 999*10 = 9990 times INSTR, and will not find a match. Only with lngIndex = 1000 and lngItemIndex = 1 will it find a match. In total the code executes INSTR 9990 + 1 = 9991 times.