Debugging a function

[I Maycotte]

Hello everyone,

I am having trouble debugging the following function


Code
-------------------
Function RATELOOKUP(ByVal strProdName As String, ByVal strPremType As String, ByVal strTrailOption As String, _
ByVal strCompSched As String, ByVal nPremBand As Integer, ByVal strPremRange As Variant, _
ByVal strAgeBand As String)

Dim wsAutoFilter as Worksheet
Dim rgeProdName
Dim rgePremType
Dim rgeTrailOption
Dim rgeCompSched
Dim rgePremBand
Dim rgePremRange
Dim rgeAgeBand
Dim rgeSelected
Dim CellValue

Set wsAutoFilter = Worksheets("Autofilter")
With wsAutoFilter
rgeProdName = .Range("ProdName") 'A:A
rgePremType = .Range("PremType") 'B:B
rgeTrailOption = .Range("TrailOption") 'C:C
rgeCompSched = .Range("CompSched") 'D
rgePremBand = .Range("PremBand") 'E:E
rgePremRange = .Range("PremRange") 'F:F
rgeAgeBand = .Range("AgeBand") 'G:G
rgeSelected = .Range("Selected") 'H:H
End With

With Application.WorksheetFunction
CellValue = .Index(rgeSelected, .Match(strProdName & strPremType & strTrailOption & _
strCompSched & nPremBand & strPremRange & strAgeBand, rgeProdName & rgePremType & _
rgeTrailOption & rgeCompSched & rgePremBand & rgePremRange & rgeAgeBand, 0))
End With
'CellValue = "=INDEX(" & rgeSelected & ",MATCH(" & strProdName & "&" & strPremType & "&" &
' strTrailOption & "&" & strCompSched & "&" & nPremBand & "&" & strPremRange & "&" & _
' strAgeBand & "," & rgeProdName & "&" & rgePremType & "&" & rgeTrailOption & "&" & _
' rgeCompSched & "&" & rgePremBand & "&" & rgePremRange & "&" & rgeAgeBand & ",0))"

RATELOOKUP = CellValue
End Functio
-------------------


I get the error: "Type Mismatch" on the CellValue
.Index(rgeSelected... line, and I don't know what to do to fix it. I
you could help degug, I'd appreciate it. Thank you

 

[Special-K]

Sounds like you got a string where a number is expected or vice versa in
the spreadsheet

 

[MattShoreson]

try something like...

dim rngSelected as range

with wksautofilter.....

set rngSelected = .range("Selected"

 

[I Maycotte]

Thanks for the prompt reply. I double checked the inputs (strings where
integers should be and vice versa) and those are correct. I have also
heeded your advice Matt; however, it has not resolved the issue. One
thing I noticed was that when I ran the cursor over the .Range(*)
lines, it came up with .Range(*) = <Object variable or With block not
set>. This could be a related issue or a different one. What do you
make of this?

 

[MattShoreson]

Your error message is saying that the range object is not set.

As part of your index function you cant pass in a string as the range
object when coding.

i.e. index("A2",....

you need to pass in a range object.

Declare the range... Dim rng as range

then you need to set the rng variable.

SET rng = range("A2")

then use this in the index function
index(rng,....

 

[I Maycotte]

Yes. I understand what it is saying. Nonetheless, even after *set*ting
the range variables, it gives me that error. Here is updated code:


Code:
--------------------
Function RATELOOKUP(ByVal strProdName As String, ByVal strPremType As String, ByVal strTrailOption As String, _
ByVal strCompSched As String, ByVal nPremBand As Integer, ByVal strPremRange As Variant, _
ByVal strAgeBand As String)
'
' Written by: Isaac Maycotte
' Actuarial Intern
' Summer 2006
'
' Function: This function will function like a "nested" VLOOKUP() worksheet function allowing the user
' to input several parameters to look across a number of columns with different criteria
' that define the commission rates.
'
Dim wsAutoFilter As Worksheet
Dim rngProdName As Range
Dim rngPremType As Range
Dim rngTrailOption As Range
Dim rngCompSched As Range
Dim rngPremBand As Range
Dim rngPremRange As Range
Dim rngAgeBand As Range
Dim rngSelected As Range
Dim CellValue

Set wsAutoFilter = Worksheets("Autofilter")
With wsAutoFilter
Set rngProdName = .Range("ProdName")
Set rngPremType = .Range("PremType")
Set rngTrailOption = .Range("TrailOption")
Set rngCompSched = .Range("CompSched")
Set rngPremBand = .Range("PremBand")
Set rngPremRange = .Range("PremRange")
Set rngAgeBand = .Range("AgeBand")
Set rngSelected = .Range("Selected")
End With

With Application.WorksheetFunction
CellValue = .Index(rngSelected, .Match(strProdName & strPremType & strTrailOption & _
strCompSched & nPremBand & strPremRange & strAgeBand, rngProdName & rngPremType & _
rngTrailOption & rngCompSched & rngPremBand & rngPremRange & rngAgeBand, 0))
End With

RATELOOKUP = CellValue
End Function

 

[MattShoreson]

whats the object you're feeding into the worksheet match function?

it's not set.

so in the same way you set rngselected = .range("Selected"), you nee
to do the same with the range you pass in to the match function

 

[I Maycotte]

Well, I'm passing multiple ranges -- those that are not rngSelected.
Obviously, I have to pass mutliple ranges in order to match multiple
criteria.

Also, by deleting the period in front of .Range(*) I do not get "Object
variable or with block not set." This still creates the type mismatch.
I am starting to think that it is how I am passing the arguments in the
worksheet function.

As I said, I am passing multiple ranges as one argument, like I would
in excel.

 

[MattShoreson]

I see what you're trying to do...

...and you cant do it this way.

you're trying to group ranges and matches in one worksheet function.
No can do excel not clever enough.

Post a bit of your table and criteria. Lets see what you're trying t
achieve

 

[I Maycotte]

Here are two rows of my data. I am trying to get the last number (
rate) that is dependent on the first 7 columns.

Product 1 A Trail A 5 80-85 0.00%
Product 2 B No Trail D 1 0-40 9.00%


-- Isaa

 

[I Maycotte]

[Edit]
Product1 A 'Trail' A 5 80-85 0.00%
Product2 B 'No Trail' D 1 0-40 9.00

 

[MattShoreson]

Instead of using a UDF, try an array formula instead.

With the sample set you provided being in A1:G2.

{=SUM((A1:A2="Product2")*(B1:B2="B")*(C1:C2="No
Trail")*(D12="D")*(E1:E2=1)*(F1:F2="0-40")*G1:G2)}

search under array formula - there's plenty you can do with them.

 

[I Maycotte]

Thanks Matt,

An array formula would be GREAT if one of the fields I need to chec
was not empty, and an array formula will return #Value! with any empt
cell. But I agree there are many things you can do with them, and
might have to change things up for the sake of simplicity. Thank you
once again.

-- Isaa