Thread: [SOLVED] Identical code, different output - reading from two different Excel files

Dear members,

Sorry to disturb you again. I have two code snippets that are identical (except for one variable name):

Snippet 2

        For i = 2 To Application.CountA(Range("A:A"))
            For j = 2 To Application.CountA(Range("A:A"))
                If TO_Sheet_OFA.Range("A" & i) = Filename_TO.Sheets("Extra_days").Range("A" & j) Then
                    TO_Sheet_OFA.Range("F" & i) = Filename_TO.Sheets("Extra_days").Range("B" & j)
                End If
            Next j
        Next i

Snippet 1

        For i = 2 To Application.CountA(Range("A:A"))
            For j = 2 To Application.CountA(Range("A:A"))
                If TO_Sheet_CTO.Range("A" & i) = Filename_TO.Sheets("Extra_days").Range("A" & j) Then
                    TO_Sheet_CTO.Range("F" & i) = Filename_TO.Sheets("Extra_days").Range("B" & j)
                End If
            Next j
        Next i

Snippet 1 works, and copies the right information. Snippet 2 doesn't work, as column F remains empty after the code has been executed.
My suspicion at first that the source file (Filename_From, not shown in the text above) might be corrupted. I recreated the file, but that did not help.

I have attached the two code files in their entirity below. What are your theories?

EDIT: added workbooks.

Why would you have two identical pieces of code barring a variable name?

Why not use one piece of code, the piece that works, and pass the sheet name to it as a variable? One variable that contains the sheet name.

You're doubling the maintenance effort and, as you are finding out, the time to debug code that works in one place but not in another.

Regards, TMS

Why don't you attach the workbook?

Probably this suffices, assuming 'TO_Sheet_OFA' refers to a worksheet

For each cl in TO_Sheet_OFA.columns(1).specialcells(2)
   Filename_TO.Sheets("Extra_days").columns(1).find(cl.value,,xlvalues,xlwhole).offset(,5)=cl.offset(,1).value
next

Basically if you use more than 1 workbook you should refer to those workbooks properly, the same applies to worksheets:

For each cl in workbooks("book1.xls").sheets("sheet12").columns(1).specialcells(2)
  workbooks("book2.xls").sheets("sheet4").columns(1).find(cl.value,,xlvalues, xlwhole).offset(,5)=cl.offset(,1).value
next

I'm clearly missing something as there's no forms or code in either of the templates that you have uploaded.


Regards, TMS

In VBA you'd better not use 'activate' nor 'select'.
You can refer to workbooks, worksheets directly as I showed in my example. As yuo can see you don't even need objectvariables to accomplish this.