Type Mismatch!!!
 

Okay so I'm a little rusty on my Excel VBA skills and I can't figure
out why I'm getting a type mismatch error for the following line in my
code:

Dim CopyEventDateCell As Date
Dim MyRow as integer
MyRow = 2

CopyEventDateCell = Workbooks("IT Operations Summarized Change
Report TEST.xls").Worksheets("IT Operations").Cells(MyRow, 6).Value

The only thing I can think of is it's an issue with the date format?
The cell value I'm referencing is in date format...I'm baffled, help!!!

Thanks,
Shelley

Type Mismatch!!!
 

I just figured it out...I need to dim copyeventdatecell as variant,
duh!!!

Sorry if I wasted anyone's time...

Shelley

Type Mismatch!!!
 

Perhaps a little testing:

Dim CopyEventDateCell As Date
Dim MyRow as Long
Dim rng as Range
MyRow = 2

set rng = Workbooks("IT Operations Summarized Change
Report TEST.xls").Worksheets("IT Operations").Cells(MyRow, 6)

msgbox typename(rng.value) & ", " & isdate(rng.value)

--
Regards,
Tom Ogilvy

"sbowman" wrote:

Okay so I'm a little rusty on my Excel VBA skills and I can't figure
out why I'm getting a type mismatch error for the following line in my
code:

Dim CopyEventDateCell As Date
Dim MyRow as integer
MyRow = 2

CopyEventDateCell = Workbooks("IT Operations Summarized Change
Report TEST.xls").Worksheets("IT Operations").Cells(MyRow, 6).Value

The only thing I can think of is it's an issue with the date format?
The cell value I'm referencing is in date format...I'm baffled, help!!!

Thanks,
Shelley

Type Mismatch!!!
 

You shouldn't have to if the cell contains a date.

As a demonstration, this works fine for me:

Sub tester1()
Dim dt As Date
ActiveCell.Value = Date
dt = ActiveCell.Value
MsgBox dt
End Sub

--
Regards,
Tom Ogilvy


"sbowman" wrote:

I just figured it out...I need to dim copyeventdatecell as variant,
duh!!!

Sorry if I wasted anyone's time...

Shelley

Type Mismatch!!!
 

Darn it, using variant doesn't work for me because i have to do
comparisons later on in the code (less than, equal to, etc.). The cells
I'm referencing are formatted as dates but some of them do have some
text in them which may be throwing off my code. I really need to dim as
date, help!!!

Shelley

Type Mismatch!!!
 

formatting has no effect on what is stored in the cell. It appears you have
a text string stored in the cell. You will have to parse out the date
portion and convert it to a date if you need a date serial number.

--
Regards,
Tom Ogilvy


"sbowman" wrote:

Darn it, using variant doesn't work for me because i have to do
comparisons later on in the code (less than, equal to, etc.). The cells
I'm referencing are formatted as dates but some of them do have some
text in them which may be throwing off my code. I really need to dim as
date, help!!!

Shelley

Type Mismatch!!!
 

How can I parse the date out of the field? It's completely random, some
people put in a description with the date, others put in just the date,
etc. The way people enter this data into the spreadsheet will not
change, I'm creating a workaround to generate a report. I'm going to be
creating an Access application for this mess in the future :-)

Shelley