Recognize Data Type in VBA

[bergy]

Hi

I have a very long column of data that is supposed to be numbers only. The macro takes each number and then performs a mathematical calculation with the number. The problem is that the data is slightly corrupt and there is text in a few cells. My macro craps out because of the data type issue. If the cell is text all I need to do is skip the cell and go on to the next cell. Is there a VBA form of ISNUMBER or ISTEXT? Thanks

 

[bergy]

Nevermind...

I just found the TypeName function.

 

[Legacy 98055]

Hi.
Check out IsNumeric in VBA help.
Also TypeName for more thorough tests.

<pre>
Sub I_Got_Your_Number()
Dim MyVariant As Variant, NumberOrNo As Boolean, MyDataType As String

MyVariant = "8998T98"
NumberOrNo = IsNumeric(MyVariant)
MyDataType = TypeName(MyVariant)
'NumberOrNo returns FALSE
'MyDataType returns "String"

MyVariant = "0234202"
NumberOrNo = IsNumeric(MyVariant)
MyDataType = TypeName(MyVariant)
'NumberOrNo = returns TRUE
'MyDataType returns "String"

MyVariant = 85674
NumberOrNo = IsNumeric(MyVariant)
MyDataType = TypeName(MyVariant)
'NumberOrNo = returns TRUE
'MyDataType returns "Long"

End Sub

</pre>
Tom

 

[Joe Was]

This is a utility I use it will give most of the format and Data info. for a cell in a MsgBox. This may help. JSW

Sub myCDType()
'Cell type(Data, Format & Formula test), of single cell in MsgBox.
'Load code in "Module."
'Best if run by Hot-key(Macros-Macro-Options).
'
'Test for formula in cell.
Dim myF As String
If Selection.HasFormula = True Then myF = " and contains a ." Else _ myF = " and contains [No Formula]." 'Test Alignment of selection? If Selection.HorizontalAlignment = xlGeneral Then X = "General" Else If Selection.HorizontalAlignment = xlLeft Then X = "Left" Else If Selection.HorizontalAlignment = xlCenter Then X = "Center" Else If Selection.HorizontalAlignment = xlRight Then X = "Right" Else If Selection.HorizontalAlignment = xlFill Then X = "Fill" Else If Selection.HorizontalAlignment = xlJustify Then X = "Justify" Else If Selection.HorizontalAlignment = xlCenterAcrossSelection Then X = "Center Across Selection" Else If Selection.HorizontalAlignment = xlDistributed Then X = "Distributed" Else If Selection.VerticalAlignment = xlTop Then Y = "Top" Else If Selection.VerticalAlignment = xlCenter Then Y = "Center" Else If Selection.VerticalAlignment = xlBottom Then Y = "Bottom" Else If Selection.VerticalAlignment = xlJustify Then Y = "Justify" Else If Selection.VerticalAlignment = xlDistributed Then Y = "Distributed" Else Select Case True 'Test cell data type & format, return to MsgBox for display. 'Note: For some reason the case-order is important! ' 'Cell is Blank, Cell Format & Formula? Case IsEmpty(Selection): MsgBox "The Cell Data is: [Blank] with a [" _ & Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _ & "The Column is : [" & Selection.ColumnWidth & "] wide" _ & " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _ & "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]." 'Cell is Text, Cell Format & Formula? Case Application.IsText(Selection): MsgBox "The Cell Data is: [Text] with a [" _ & Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _ & "The Column is : [" & Selection.ColumnWidth & "] wide" _ & " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _ & "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]." 'Cell is Date, Cell Format & Formula? Case IsDate(Selection): MsgBox "The Cell Data is a: [Date] with a [" _ & Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _ & "The Column is : [" & Selection.ColumnWidth & "] wide" _ & " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _ & "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]." 'Cell is Time, Cell Format & Formula? Case InStr(1, Selection.Text, ":")<> 0: MsgBox "The Cell Data is a: [Time] with a [" _ & Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _ & "The Column is : [" & Selection.ColumnWidth & "] wide" _ & " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _ & "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]." 'Cell is Logical, Cell Format & Formula? Case Application.IsLogical(Selection): MsgBox "The Cell Data is: [Logical] with a [" _ & Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _ & "The Column is : [" & Selection.ColumnWidth & "] wide" _ & " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _ & "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]." 'Cell is Error, Cell Format & Formula? Case Application.IsErr(Selection): MsgBox "The Cell Data is an: [Error] with a [" _ & Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _ & "The Column is : [" & Selection.ColumnWidth & "] wide" _ & " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _ & "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]." 'Cell is Value, Cell Format & Formula? Case IsNumeric(Selection): MsgBox "The Cell Data is a: [Value] with a [" _ & Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _ & "The Column is : [" & Selection.ColumnWidth & "] wide" _ & " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _ & "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]." End Select End Sub [SIZE=2][COLOR=gray]This message was edited by Joe Was on 2002-06-19 11:53 [/COLOR][/SIZE]