For each... Problem 2

[Igwiz]

Hi,

Got a problem with a For Each loop

This is my code:

Sub D()

Worksheets("DG&quot.Select

For Each cell In Worksheets("CDS&quot.Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Cells

Unfortunately it bombs out at the For Each line. I have tried different alternatives and it appears to fall over as soon as I put in the worksheets("&quot part of the for each range reference. Can you not call a for each loop reference from a different sheet to the active one?

Many thanks,

Ig

 

[RiazKhanmohamed]

try typing in the full spreadsheet name with .xls extension. then try it with network location if you are running it from a folder that is different to the on with the excel sheets.

 

[combo]

You can, but in this way:
[tt]With Worksheets("CDS&quot
For Each cell In .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Cells
....
Next cell
End With[/tt]

combo

 

[TonyJollans]

Hi Ig,

The problem is that the Cells property, without qualification, belongs to the Application object and refers to cells on the Active Sheet which is "DG" (because you have Selected it). To explicitly refer to cells on worksheet "CDS" you must qualify the references to Cells; you do not, however, need to qualify the Range itself as you are defining it explicitly, and also the .Cells on the end does nothing at all so may as well not be there, and you end up with ..

For Each cell In Range(Worksheets("CDS").Cells(1, 1), Worksheets("CDS").Cells(1, 1).End(xlDown))

Enjoy,
Tony

 

[Igwiz]

Great guys. Used yours Tony. Thanks.