Type Mismatch Error in Excel 2007

[Bryan03Cobra]

Hello everybody, I've browsed this forum a lot so I figured it was time to register and ask some questions of my own. This seems like an easy question yet I can't find the answer.

This code works fine in Excel 2003 but I get a Runtime Error 13 message in '07. Here is the code:

Sub First3()

Dim Ded As Integer
Dim Coins As Double
Dim OOP_Max As Integer
Dim FamDedFactor As Double
Dim i As Integer
Dim Plan_Value As Double
For i = 3 To 5

Sheets("Data and Pricing").Select
Ded = Cells(i, 7).Value
Coins = Cells(i, 11).Value
OOP_Max = Cells(i, 12).Value
FamDedFactor = Cells(i, 10).Value

Sheets("User Input").Select

Range("EV11").Value = Ded
Range("EV20").Value = Coins
Range("EV21").Value = OOP_Max
Range("EV14").Value = FamDedFactor
Calculate

Sheets("Summary").Select
Plan_Value = Range("V123").Value
Sheets("Data and Pricing").Select
Cells(i, 13).Value = Plan_Value
Next i

End Sub

The bolded area is where the error comes from.

Thanks in advance.

Bryan

 

[AlphaFrog]

You Dimentioned Plan_Value as type Double

Dim Plan_Value As Double

If Range("V123").Value is not of type Double e.g. it's blank or text, you'll get an error.

You could replace this...

Sheets("Summary").Select
Plan_Value = Range("V123").Value
Sheets("Data and Pricing").Select
Cells(i, 13).Value = Plan_Value

With just this...

Sheets("Data and Pricing").Cells(i, 13).Value = Sheets("Summary").Range("V123").Value

 

[Bryan03Cobra]

Thanks a lot for your response. This new code definitely works on my 2003 Excel, I will post again later when I can give this a shot on one of my co-worker's 2007.

 

[Bryan03Cobra]

Ok, the macro runs fine now in 2007, but I'm getting #VALUE everywhere. I've traced the error to this formula:

=+LookupAdjFactor(Contractual4aMH,"Start_IP_MHSA","UnlimitedIPMHSA","Tables",3)

again, this works fine in 2003 but returns a #VALUE for 2007. Any ideas?

 

[Bryan03Cobra]

Ok, sorry, I just took a quick lesson in UDF's. I had no clue what that function above was doing. Apparently it isn't working in 2007. I'll paste the code used for the function below. Is there anything special I need to do with 2007 so it recognizes the UDF? I've enabled all addins.

Function LookupAdjFactor(LookupValue As Double, sMinimum As String, sMaximum As String, sTableSheet As String, column As Integer)
Dim LowerLimitValue As Double, UpperLimitValue As Double
Dim LowerLimitFactor As Double, UpperLimitFactor As Double
Dim i As Integer
Dim endrow As Integer

endrow = CountRows(Range(Range(sMinimum), Range(sMinimum).End(xlDown)))
Select Case LookupValue

'if the input is greater than the maximum in the table
'use the factor for the maximum

Case Is >= Range(sMaximum).Value
LookupAdjFactor = Range(sMaximum).Offset(0, column)

'if the input is less than the minimum in the table
'use the factor for the minimum

Case Is <= Range(sMinimum).Value
LookupAdjFactor = Range(sMinimum).Offset(0, column)

'otherwise interpolate if necessary

Case Else
With Sheets("Controls")
For i = 0 To endrow
If LookupValue = Range("Start_OP_MHSA").Offset(i, 0) Then
LookupAdjFactor = Range("Start_OP_MHSA").Offset(i, column)
Exit For
End If

If LookupValue < Range(sMinimum).Offset(i, 0) Then
LowerLimitValue = Range(sMinimum).Offset(i - 1, 0)
LowerLimitFactor = Range(sMinimum).Offset(i - 1, column)
UpperLimitValue = Range(sMinimum).Offset(i, 0)
UpperLimitFactor = Range(sMinimum).Offset(i, column)
LookupAdjFactor = (1 - (LookupValue - LowerLimitValue) / (UpperLimitValue - LowerLimitValue)) * LowerLimitFactor + (LookupValue - LowerLimitValue) / (UpperLimitValue - LowerLimitValue) * UpperLimitFactor
Exit For
End If
Next i
End With


End Select

End Function