Run time error 13, type mismatch

[cellist]

Dim days As Variant
Dim hours As Variant
Dim holOptElig As Variant
Dim wb As Workbook
Set wb = Workbooks("Salary and Hourly Summary 2009.xls")
days = wb.Names("MikeNbrHolidays").Value
hours = wb.Names("MikeTotalHolHours").Value
If days > 0 Then
holOptElig = hours / days <== error on this statement
End If

In "Salary and Hourly Summary 2009.xls", MikeNbrHolidays and
MikeTotalHolHours are formatted as number/0 decimal places and
number/1 decimal places respectively, and their values are 8 and
43.2. I get run time error 13, type mismatch when I run this code.

TIA,

Phil

 

[Barb Reinhardt]

I think I'd try this

holOptElig = val(hours) / val(days)

The value may not be numeric for some reason.

 

[Mike H]

Hi,

The syntax your using is returning the address of the named range try this
instead

Dim days As Variant
Dim hours As Variant
Dim holOptElig As Variant
Dim wb As Workbook
Set wb = Workbooks("Salary and Hourly Summary 2009.xls")
days = wb.Names("MikeNbrHolidays").RefersToRange
hrs = wb.Names("MikeTotalHolHours").RefersToRange
If days > 0 Then

holOptElig = hrs / days '<== error on this statement
End If

Mike

 

[Dave Peterson]

I'd check both those variables.

dim OkToCalc as boolean

OkToCalc = true
if isnumeric(hours) = false then
msgbox "hours not numeric"
oktocalc = false
end if
if isnumeric(days) = false then
msgbox "days not numeric"
oktocalc = false
else
if days = 0 then
msgbox "Days is 0--division by 0 not possible"
oktocalc = false
end if
end if

if oktocalc then
holOptElig = hours / days
else
'what should happen here?
end if

 

[cellist]

Thanks Mike, that fixed it. deja vu to my C programming days. You know, does
the variable hold the value I want or is it a pointer to the value I want.

 

[cellist]

Barb, thanks for your reply. Always a good practice to check for valid data,
but in this case it was a differentl problem.

 

[cellist]

Dave, thanks for your reply. Turns out it was a different kind of problem in
this case, but your suggestion is right on as a defensive programming device.

 

[Dave Peterson]

I see that now.

It usually a good idea to indicate the line that actually causes the error.
Else each responder may see problems in other portions and correct that.
(That's what happened to me!)