Problems with Excel VBA function IsDate

[akn846]

I am having trouble understanding why the Excel function IsDate is returning false when I am trying to read a cell which has Date formatting applied to it.

Basically the program is sequentially going through a column of status' - when there is an open entry, it then looks at the corresponding cell to determine whether the action date is earlier than the current system date (and therefore whether or not it needs to be actioned).

Despite the fact that I have applied Date formatting to the entire column, when I run the IsDate() function against a cell containing one of these dates I get a result of FALSE.

Can anyone suggest why this should be happening?

is there a way of forcing the contents of a cell to be treated as though it was a date - so it can be assigned to a Date type variable for comparison?

Any help would be much appreciated, as this is starting to annoy me!

Thanks

Andy

 

[ttmac]

Andy,

Can you post your code so that I can look at whats happening?

Tom

 

[akn846]

Tom

Here's the code as it stands just now - as I said I've made sure that the column format is set to Date - so I am not sure why it is going wrong. Well I do know, I just don't know how I can assign a variable I declare to be of type Date to be assigned the contents of a cell which is also of Date type - if this makes any sense?

Thanks for having a look at this though.

Andy
Dim rowcount As Integer
Dim currentrow As Integer
Dim lastreference As Variant
Dim searchrange As Variant
Dim actiondate As Variant

Dim currentdate As Date
Dim datecell As Variant
Dim x As Variant
Dim rowselect As Variant
Dim ajk As Boolean



currentdate = Trim(Int(Now))

rowcount = Application.Sheets("Change Log&quot.UsedRange.Rows.Count
lastreference = "J" + Trim(Str(rowcount))
searchrange = "J3:" + lastreference



currentrow = 2

For Each cell In Range(searchrange)
currentrow = currentrow + 1

If UCase(cell.Value) = "CLOSED" Then
' do nothing
Else
datecell = "I" + Trim(Str(currentrow))
actiondate = Range(datecell).Text
' MsgBox actiondate




If actiondate < currentdate Then
MsgBox &quot;Date is less than today&quot;
x = Trim(Str(currentrow))
rowselect = &quot;A&quot; + x + &quot;:J&quot; + x

Range(rowselect).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If

End If

Next

End Sub

 

[rmikesmith]

Andy,

Try replacing this line

actiondate = Range(datecell).Text

with

actiondate = Range(datecell).Value

HTH
M. Smith

 

[akn846]

Mike

I've tried that and I still end up getting a type mismatch when I make actiondate of variable type Date rather than variant - any other ideas would be appreciated!

Cheers

Andy

 

[acron]

Just an idea, but try

If DateValue(actiondate) < currentdate Then. etc..

as actiondate is not being seen by VBA as a date.

A.C

 

[akn846]

acron

Tried that as well - still get a type mismatch.

Am I missing something really simple here - as this is another code segment I've tried in order to get the basics of assigning the contents of a cell to a conditional test, using the datevalue as you suggested. Dim andy As String


andy = Range(&quot;a1&quot.Value

If DateValue(andy) = Now Then
MsgBox &quot;Date chek has worked&quot;
End If

Cheers

 

[ttmac]

Andy,

From what I can see, you are looping through rows containing info in the worksheet. However, you are starting processing the values at row 2 i.e. currentrow = 2 and using the cell references to drive your loop i.e. For each cell in cell range. This will have the effect of starting at row 2 and will continue until cellrange + 2

So, for example, if you have 8 rows the loop will run until the values up to and including row 10 have been processed. (I hope this makes sense)

isdate() when applied to the values in roe 9 and 10 will return false as expected as the function returns false if the contents cannot be evaluated as a date.

If the above does not make it any clearer post again

Tom.

 

[akn846]

Tom

Thanks for the suggestion, but this isn't quite correct in terms of the program at the top of this posting.

What happens is that there are two header rows for the spreadsheet, so basically the first iteration round the loop should start from row 3.

The loop end is constrained as I count the number of rows which exist within the sheet - and make the search range go from row 3 to the maximum number of rows in the sheet.

The main problem I can see is that for some reason I just cannot get a value within a cell to be assigned to a variable defined as being of type Date - I have to assign it to a Variant, and I can't perform date calculations on a Variant.

Even trying to convert the cell contents to a Date variable type with the DateValue function blows up with a type mismatch - it is getting very silly!!

So any more brainwaves would be appreciated!

Thanks

Andy

 

[akn846]

does anyone know of a way to determine what data type the contents of a cell are? The fact that I cannot get a simple program to be able to implement the DateValue function on a cell leads me to think there is something very strange about the formatting of the cells within my spreadsheets

Thanks

 

[rmikesmith]

Andy,

This will not help you directly but in trying to duplicate what you are seeing here are some observations:

I declared a variable as type Date. I assign it the value of the ActiveCell. No matter whether the ActiveCell is a date, is blank, is text, or a number that cannot be converted to a date does not cause an error in the assignment (i.e. no &quot;Type Mismatch&quot; error). In those cases its value is simply 12:00:00AM.

As you say, it's silly and, of course, frustrating. If possible could you send a copy of your workbook?

mike.smith@delphiauto.com

Regards,
Mike

 

[ttmac]

Andy,

The following piece of code works: -

'Option Explicit
Sub tester()

Dim rowcount As Integer
Dim currentrow As Integer
Dim lastreference As Variant
Dim searchrange As Variant
Dim actiondate As Date

Dim currentdate As Date
Dim datecell As Variant
Dim x As Variant
Dim rowselect As Variant
Dim ajk As Boolean



currentdate = Trim(Int(Now))

rowcount = Application.Sheets(&quot;Change Log&quot.UsedRange.Rows.Count
lastreference = &quot;J&quot; + Trim(Str(rowcount))
searchrange = &quot;J3:&quot; + lastreference

' Dim cell As Object




currentrow = 2

For Each cell In Range(searchrange)
currentrow = currentrow + 1
'MsgBox cell.Value & &quot; &quot; & cell.Address
If UCase(cell.Value) = &quot;CLOSED&quot; Then
' do nothing
Else
datecell = &quot;I&quot; + Trim(Str(currentrow))
actiondate = Range(datecell).Text
MsgBox actiondate




If actiondate < currentdate Then
MsgBox &quot;Date is less than today&quot;
x = Trim(Str(currentrow))
rowselect = &quot;A&quot; + x + &quot;:J&quot; + x

Range(rowselect).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If

End If

Next

End Sub

I did get the type mismatch error you referred to. This was due to the fact that the Application.Sheets(&quot;Change Log&quot.UsedRange.Rows.Count returned a value for cells which was beyond the range for which you wanted to process. When I cleared the cell contents beyond the range for processing using Edit | Clear | All the problem was sorted

Tom.

 

[rmikesmith]

Andy,

Sounds like Tom may be onto something. If that doesn't do it here is a function you can use to check type of data in a cell. Put it into a standard code module. You can then reference it in a vba procedure or use it as a worksheet function:

Function VType(Inp) As String

  Select Case VarType(Inp)
    Case vbEmpty
      VType = "Empty"
    Case vbNull
      VType = "Null"
    Case vbInteger
      VType = "Integer"
    Case vbLong
      VType = "Long"
    Case vbSingle
      VType = "Single"
    Case vbDouble
      VType = "Double"
    Case vbCurrency
      VType = "Currency"
    Case vbDate
      VType = "Date"
    Case vbString
      VType = "String"
    Case vbObject
      VType = "Object"
    Case vbError
      VType = "Error"
    Case vbBoolean
      VType = "Boolean"
    Case vbVariant
      VType = "Variant"
    Case vbDataObject
      VType = "Data Object"
    Case vbDecimal
      VType = "Decimal"
    Case vbByte
      VType = "Byte"
    Case vbUserDefinedType
      VType = "User Defined Type"
    Case vbArray
      VType = "Array"
    Case Else
      VType = "Unknown"
  End Select
End Function

Regards,
Mike

 

[ttmac]

TypeName(actiondate)

would also work

Tom.

 

[rmikesmith]

Tom,

TypeName will tell you the variable type but not what type a worksheet cell's data is. TypeName just returns &quot;Range&quot; in that case.

Regards,
Mike

 

[akn846]

Tom

I am still unable to get the code to run successfully - so I don't know why this should work for you and not for me.

The problem occurs on the 7th row of the sheet - so I don't understand what is going on.

Mike, I shall email you a copy of the workbook as requested - this is starting to really do my head in.

thanks

Andy

 

[akn846]

Mike/Tom

Sussed it.

although I thought that the date values had been entered as dates, with appropriate date formatting applied - it looks as though they were entered as just text.

Many, many thanks for your help on this

Cheers

Andy

 

[ttmac]

Andy,

You're welcome.

Mike,

Try the following function: -

Public Function vProtoTYPE(inp As Variant) As String

vProtoTYPE = TypeName(inp.Value)

End Function

 

[rmikesmith]

Tom,

That works on the worksheet. Nice and compact, also. Thanks.

Mike