| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rephrasing argument for typename and a different result
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rephrasing argument for typename and a different result
Hari,
Did you compile the code and not get any warnings? Looks like the following line has one too many left parentheses. If TypeName((Sheets(sheetName).Cells(n, 2)) < "Date" Then How about: If TypeName(Sheets(sheetName).Cells(n, 2)) < "Date" Then Troy "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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rephrasing argument for typename and a different result
Hi Troy,
Im sorry, I added a parentheses by mistake when posting the message to the group. The original problem still persists. Regards, Hari India "TroyW" wrote in message ... Hari, Did you compile the code and not get any warnings? Looks like the following line has one too many left parentheses. If TypeName((Sheets(sheetName).Cells(n, 2)) < "Date" Then How about: If TypeName(Sheets(sheetName).Cells(n, 2)) < "Date" Then Troy "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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rephrasing argument for typename and a different result
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rephrasing argument for typename and a different result
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rephrasing argument for typename and a different result
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Rephrasing Interior.ColorIndex 36 question. | New Users to Excel | |||
| Function argument correct result but answer 0 in cell? | Excel Worksheet Functions | |||
| Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
| vlookup based on random result returns incorrect result | Excel Worksheet Functions | |||
| dynamic arrays as an argument/result in programmed functions | Excel Programming | |||
![Hari[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
