column number of non-selected cells

[Adrian]

This works...

Sub test()
For Each cell In Selection
MsgBox cell.Column
Next
End Sub

But this does not work...
Sub test()
Set region = Selection
For Each cell In region
MsgBox cell.Column
Next
End Sub

I need my macro to work without selecting the cells I'm
working on, so please can someone explain/help?

(you'll understand that the above is a greatly simplified
piece of code that focuses on my problem.
In fact, "region" is the result of a fairly complicated
calculation, and I want to use both the .Value and .Column
of "cell" in the code within the loop.)

 

[Tom Ogilvy]

It works fine for me, as expected.

Perhaps the devil is in the details.

does it raise an error? Give the wrong results?

 

[Frank Kabel]

Hi Adrian
without nowing what 'region' is this is complicated. You have to create
a range (based on your claculations).
So you may post the lies to get to 'region'

 

[Brad]

Your second example works for me.

 

[adrian]

I get run time error 424
Object Required.

I'm running Excel2000 and Windows2000.

Frank.Kabel asks the details to get "region", but the
examples I posted do not work for me. My real macro fails
in exactly the same way as my example.

 

[Tom Ogilvy]

Sounds like selection isn't a range

for example, try this

Sub test()
Set region = Selection
Msgbox typename(selection)
End Sub

If you are using Excel 97 and running your code from a command button, try
changing the TakeFocusOnClick Property to false.

 

[Adrian]

Sub test()
Set region = Selection
Msgbox typename(region)
Msgbox typename(selection)
End Sub

This was very interesting.
If the selection was a single cell, then region reflected
the contents of that cell. (eg String) But if the
selection was multiple cells then region was Variant.
Selection was always Range, but region was never Range.

This mystery remains unsolved. I've got round my
immediate problem by changing round the sequence of
chunks of logic in my macro, so that it became
permissible to select "region" after all.

Many thanks for trying to help.
Until another day....