"Tim Childs" wrote in message
...
On 03-Mar-17 8:00 PM, Peter T wrote:
"Tim Childs" wrote in message
Hi

In VBA I want to get the cell information on a cell e.g. in the
Application itself, I can put:
=CELL("type",A1)
in Cell B1
However, I would like to extract the same information about (multiple)
cells directly from VBA itself i.e. in a procedure, without entering a
formula into an Excel cell.
Please can you give me the VBA syntax for that.

CELL + "type" doesn't tell you much - the cell contains a string, is
empty, or anything else. Other VBA functions can tell you a lot more but
give some idea of what you're looking for and what you want to do with
it.

Peter T


Thanks for response

I want to distinguish between cells that have been output as labels and
those that are values. In the latter, dates are numbers and in the former,
I will use TextToColumns to convert the "date labels" to proper date
values. I hope that clarifies my request, if not, please do say so.

First thing to try is simply see if the string dates will coerce to date
values, if any 'numbers' return format as date.
=(A1)*1

and look for #VALUE! errors

If that doesn't fix them try this UDF

Function DataType(cel As Range)
Dim s As String
Select Case VarType(cel)
Case vbBoolean: s = "Boolean"
Case vbDate: s = "Date"
Case vbDouble: s = "Double"
Case vbEmpty: s = "Empty"
Case vbString: s = "String"
Case vbError: s = "Error"
Case Else: s = "other"
End Select

DataType = s
End Function

=DataType(A1)

You might also try F5, Special, Constants and//or Formulas and tick only the
Text

Peter T