Must Manually Reformat Date Column Due to Type Mismatch Run-

steveski

Board Regular
Joined
Feb 9, 2004
Messages
55
I have some code that steps through a Data Entry sheet and polls a column of dates (Column “I”). I get a “: Type mismatch: at the “Sdate = Cells(i, "I").Value” statement. If I go to the Data Entry sheet, select the entire column I, and format the cells as Date format, then the next time I run the code I will not get the error. However, the next time I run the code after that I will get the error again and I must go back and reformat the column to Date format. Do you have any ideas as to what may be causing this? (I am using Excel 2000.)

Thanks.

Dim Sdate As Date
With Sheets("Data Entry").Range("PTSource")
LastRow = .Rows.Count
For i = 2 To LastRow
Sdate = Cells(i, "I").Value
Etc.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Re: Must Manually Reformat Date Column Due to Type Mismatch

Qualify the cell content for date. This tested fine; modify as needed:


Dim Sdate As Date, LastRow As Long, i As Long
With Sheets("Data Entry").Range("PTSource")
LastRow = .Rows.Count
For i = 2 To LastRow
If IsDate(Cells(i, "I")) = True Then
Sdate = Cells(i, "I").Value
MsgBox Sdate
End If
Next i
End With
 
Upvote 0
Re: Must Manually Reformat Date Column Due to Type Mismatch

Thanks.

I used your code and it basically verified that there is a type mismatch. But it has me stumped, because these are actually dates in the Cells(i, "I").

Can I change the format of each value returned by Cells(i, "I") to date format?

For example,

If IsDate(Cells(i, "I")) = False Then
Convert Cells(i, "I").Value to date format (How do I do this?)

I think this would work, although I recognize it is not very elegant and does not correct the root cause of the problem.

Thanks.
 
Upvote 0
Re: Must Manually Reformat Date Column Due to Type Mismatch

Instead of formatting them individually in the loop, just add a line at the beginning of the macro before the loop to format the used range in column I in whatever date format you want, so you take care of that in one shot. The code I posted avoids errors by ignoring values that never could be dates in column I, such as text words, negative numbers, etc.
 
Upvote 0
Re: Must Manually Reformat Date Column Due to Type Mismatch

Can you teach me the code for adding the line at the beginning of the macro before the loop?

I do have data validation on this column so the user can only enter dates.

Thanks.
 
Upvote 0
Re: Must Manually Reformat Date Column Due to Type Mismatch

steveski said:
Can you teach me the code for adding the line at the beginning of the macro before the loop?
OK, below is one way, but you can teach yourself too, because stuff like this has been posted hundreds of times on this board and on the net. Try searching this site and Google to help yourself learn different ways to accomplish what you are asking. If you get stuck, post back with what you tried to search for and what you could not find.

Here is one way to accomplish your last request:

Range(("I2"), Cells(Rows.Count, 9).End(xlUp)).NumberFormat = "mm/dd/yy"

If that does not work for you, then there is something else going on in your workbook you're not telling us, maybe dealing with exactly what those values are in data validation. They might not be recognizable by Excel as dates.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top