Rephrasing argument for typename and a different result
Hi Troy,
Thanx a ton.
I am able to get what I wanted with ur method. I have used ".value" at the
end.
Thanx again
Regards,
Hari
India
"TroyW" wrote in message
...
Hari,
The TypeName function is used to inspect objects and variables and return
the data type. Take a look at the VBA Help.
CellValue == TypeName
hello == String
1234 == Double
01/01/2004 == Date
(blank) == Empty
In your snippet of code, it appears that the INTENT of the code logic is
to
inspect the CONTENTS of the cell(n,2) and determine what type of VALUE
is
in the cell. But, the way your code is currently written, the TypeName
function can only return a value of "Range" no matter what is entered into
the cell (text, numbers, dates, etc.). This is not a syntax error which
results in the program stopping, but it is a logic error that won't return
the desired result.
In order to inspect the contents of the cell correctly you must write the
code as follows (Note the addition of ".Value" to the end of the
statement):
dates = Sheets(sheetName).Cells(n, 2).Value
If TypeName(dates) < "Date" Then
-or-
If TypeName(Sheets(sheetName).Cells(n,2).Value
Please change the code to what I'm suggesting and tell me if you still
have
errors. Or, help me to understand what the intent or purpose of the code
is.
Troy
P.S. Excel has various object types (workbook, worksheet, range, chart,
etc.). You can use TypeName to inspect those objects also and return what
type they are. Your current code is asking the question what object type
is
a cell. In Excel, a cell has a type "Range". A "cell" and a "cell value"
are
two different things.
"Hari" wrote in message
...
Hi Troy,
This is strange for me.
What datatype is range ( Im a newbie...)
Also, my original question remains. Why does excel be able to work
correctly
when we write
dates = Sheets(sheetName).Cells(n, 2)
If TypeName(dates) < "Date" Then
But if the statement is written as
If TypeName((Sheets(sheetName).Cells(n, 2)) < "Date" Then
the result after running is different in the above 2 cases as originally
stated
Regards,
Hari
India
"TroyW" wrote in message
...
Hari,
Looking closer, I don't think either statement is correct. My testing
indicates that you need to qualify that you want the "Value" of the
cell,
otherwise TypeName will always return a "Range" type. Try testing
different
values of the cell with the code below.
sType1 changes depending upon what value is in the cell. sType2 is
always
"Range", independent of what the cell value is.
I think you want to use:
TypeName(Sheets(sheetName).Cells(n, 2).Value)
Troy
Sub TypeName_Example()
Dim sType1 As String
Dim sType2 As String
'''n and sheetName need to be defined.
sType1 = TypeName(Sheets(sheetName).Cells(n, 2).Value)
sType2 = TypeName(Sheets(sheetName).Cells(n, 2))
MsgBox "Type1 = " & sType1 & vbCr & _
"Type2 = " & sType2
End Sub
"Hari" wrote in message
...
Hi,
I have the following code which executes perfectly.
dates = Sheets(sheetName).Cells(n, 2)
If TypeName(dates) < "Date" Then
Sheets(sheetName).Cells(n, 2).Interior.ColorIndex = 6
NoOfErrors = NoOfErrors + 1
Else
If I rewrite the above code as given below and run it ( without
changing
any
data in the worksheet) then it highlights an error in the cell which
has
date only. How is the 2 codes different from each other?
If TypeName((Sheets(sheetName).Cells(n, 2)) < "Date" Then
Sheets(sheetName).Cells(n, 2).Interior.ColorIndex = 6
NoOfErrors = NoOfErrors + 1
Else
Regards,
Hari
India
|