nested for each

[HarryKlein]

Hi everyone,

I am working on a macro which should do the following thing:

There are two ranges, myRange1 and myRange2.
MyRange is located in the workbook when the vba is started from, myRange is on other workbook.

I want each cell of myRange1 to be searched in myRange2.

The code is something like this

Please Login or Register  to view this content.

The message what I get is, that cell2 is not set, it seems to be, that I cannot work with myRange2.

Can anybody help?
Thanks

[mmmarks]

Have you keep open the two workbooks ?

[HarryKlein]

Yes, I do, but the question is valid, the problem is somewhere here. The macro has 2 sub-s, I have defined the ranges as public variables , and I reach them from the sub, where the declaration is done, but one of them seems to be not defined in the other sub.
The errormessage refers to the cell2 variable, which also is defined as public range, but I guess, the problem is with myRange2

[JosephP]

the code itself is ok as far as syntax - what is the exact error message?

[HarryKlein]

Runtime error 91
object variable or with block variable not set

[romperstomper]

On which line?

[HarryKlein]

This is the real code. As I doesn't work like this yet as I want it to work, I have focused on the problematic part.
The errormessage refers to line which saves the workbook.

The if-thread is obviously never started, as it never is true.

Please Login or Register  to view this content.

[romperstomper]

You are referring to the loop variable (cellaN) outside the loop, where it has gone out of scope.

[HarryKlein]

do you maybe have recommendation?

[romperstomper]

You've left most of the code out so I don't know what you're trying to achieve. It could be as simple as moving the saveas line before the 'End If' or 'Next' line.

[HarryKlein]

It might be a problem, too, but what I see as a bigger problem, that cellaN never get any value.

[romperstomper]

Then you must get an error before the SaveAs line (or you still haven't posted all the code).