No more help required. I have worked out the problem. The code in the failed
example was actually saying for each column in columns() instead of for each
cell in range and of course the c.Value was failing.

Just for info, I put in a MsgBox c.Address immediately after the row of code
and as soon as I saw the answer I knew what was wrong. Don't know why I did
not think of it before.

Now I know what the problem is I can work around it. Simply select the
column and set the inputRnge to the selection and everything works fine.

Regards,

OssieMac

"OssieMac" wrote:

Hi Joel,
Joel,

Thanks for trying to help but unfortunately I get the same error message as
with the code I had.

Just in case anyone else can help, I am using Excel 2002 in case that makes
a difference and the data in the column is all numeric without headers as per
the following sample:-
24
18
24
9
18

Regards,

OssieMac

"Joel" wrote:

try

Set inputRnge = Sheets("Input Data").cells(1,1).entirecolumn


"OssieMac" wrote:

If you see this question posted twice then my apologies. Something went wrong
before and I can't see it on the forum.
I will appreciate help with the following to tell me what I am doing wrong.

I have these lines of code which work fine:-
Dim c
Dim inputRnge As Range

Set inputRnge = Sheets("Input Data").Range("A:A")
For Each c In inputRnge
If c.Value = 0 Then Exit For
'further code here

However I want to be able to set inputRnge based on the numeric value of the
column which I believe should be like this:

Set inputRnge = Sheets("Input Data").Columns(1)
inputRnge.Select 'This line for testing inputRnge and it works

For Each c In inputRnge
If c.Value = 0 Then Exit For
'further code here

The second lot of code fails on the line If c.Value = 0 Then Exit For with
the error message:
Runtime error 13 Type mismatch.

Note that the only difference between the two lots of code is the set
inputRnge and I have tested the inputRnge with the select which appears to
work so what is wrong with it?

All help greatly appreciated.

Regards,

OssieMac