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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225
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
 

steveski

Board Regular
Joined
Feb 9, 2004
Messages
55
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.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225
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.
 

steveski

Board Regular
Joined
Feb 9, 2004
Messages
55
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.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,646
Members
414,398
Latest member
dhune

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
Top