To determine the data type of the selection range

[Subodh]

I have a range of 20 column by 30 rows
I want to check all the cells of the selection and then among the
selection
find out if the cell contains integer or error value or text or some
other data type.
Thanks in advance.

 

[Nick H]

Hi Subodh,

Try here...

http://www.cpearson.com/excel/CountType.aspx

....it might not be exactly what you're looking for but it should be a
good start.

Br, Nick

 

[Bernard Liengme]

This does a debug.print foe each cell
Boolean ends ups as "Empty"
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

Sub tryme2()
Set mydata = Selection
myrows = mydata.Rows.Count
mycols = mydata.Columns.Count
Debug.Print ; "-------------"
For j = 1 To myrows
For k = 1 To mycols
thiscell = mydata(j, k)
mytype = "Empty"
If WorksheetFunction.IsNumber(thiscell) Then
mytype = "Number"
If Int(thiscell) = thiscell Then mytype = "Integer"
End If
If WorksheetFunction.IsText(thiscell) Then mytype = "Text"
If mydata(j, k).HasFormula Then mytype = "Formula"
If WorksheetFunction.IsError(thiscell) Then mytype = "Error value"
If IsDate(thiscell) Then mytype = "Date"
Debug.Print j; k; mytype
Next k

Next j
End Sub