Merle,

In a UDF you usually do just the single action. Your UDF might look at many
cells, but it returns just one result. You do NOT try and get a UDF to
return a result for the complete range (well, actually you can, but let's
keep it a bit simpler here).

So, in this case, I would not see a UDF going down a range of cells to work
out whether each is a formula, number or whatever, but rather to have a UDF
to check the first cell in your range, passing that cell as an argument to
the UDF, then the next and so on.

Thus, I see no merit in knowing here how to looping through the range, you
don't need to. Using the UDF I gave, you can put

=CellType(A1) in one cell, and
=CellType(A2) in another, each will be independently evaluated.

Does this make sense?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Merle via OfficeKB.com" <u17293@uwe wrote in message
news:5a2ac02f5c2cc@uwe...
Ah, well, I guess I was trying to be both brief and not expose my
elementary
programming (sigh). Here's the full code which might help you better help
me.


Function CellType(rng As Range)

'get column and row from rng

col = rng.Column
n = rng.Row

'proceed down column until cell isn't empty
'or 100 rows, whichever comes first

Do While IsEmpty(Cells(n, col))
If n < 100 Then
Exit Do
End If
ActiveCell.Offset(1, 0).Select
n = n + 1
Loop

'now check the cell and test its data type
'display type like D for Date, L for Logical, etc.

cur = Cells(n, col)

If IsEmpty(cur) Then
CellType = "unknown"
ElseIf IsDate(cur) Then
CellType = "D"
ElseIf cell.Value(cur) = 1 Then
CellType = "L"
ElseIf IsNumeric(cur) Then
CellType = "N"
Else
CellType = "C"
End If

End Function

I've no doubt this isn't as efficient as it could be. I couldn't figure
out
how else to traverse down the column and test each cell. Partially, I
think,
because I couldn't get my head around what your "rng" was - cell or range?
If it's a range (which makes sense to me), how does one test each cell's
value's type?

So, this is where I ended up. My apologies for not posting it before.

Thanks!

Merle

Bob Phillips wrote:
If cell.Value Then

I don't know what you mean by cell.Value(c), that is invalid syntax

I've been able to do this successfully with the one exception of
determining
whether a cell is TRUE/FALSE (and therefore for my purpose a Logical
field).
[quoted text clipped - 42 lines]

Merle

--
Message posted via http://www.officekb.com