VBA Function returning #VALUE!

Question

I'm confused as to why the following code works in one instance, then fails in another. The following gives a #VALUE! error.

Function RateFetcher(Plan_Text As String, Zip_Text As String, Sex As String, Tabacco_Use As String, Age As Integer, State As String)

Dim Plan As String
Plan = StrConv("Plan " + Plan_Text, vbLowerCase)

Dim ProperState As String
ProperState = StrConv(State, vbProperCase)

Dim Search_Range As Range
Search_Range = Worksheets(ProperState).Range("A4:A600")
MsgBox (Search_Range)

Dim Location As String

For Each c In Search_Range
    If StrConv(c.Value, vbLowerCase) = Plan And c.Offset(1, 0) = Zip_Text Then
        Location = c.Address
    End If
Next c

Dim Sex_Tab_Offset As Integer

Dim Combined As String

Combined = Sex + Tabacco_Use

If Combined = "F NO" Then
    Sex_Tab_Offset = 1
ElseIf Combined = "M NO" Then
    Sex_Tab_Offset = 2
ElseIf Combined = "F YES" Then
    Sex_Tab_Offset = 3
Else
    Sex_Tab_Offset = 4
End If

Dim Age_Offset As Integer
Age_Offset = Age - 65

RateFetcher = Worksheets(ProperState).Range(Location).Offset(Age_Offset, Sex_Tab_Offset).Value
End Function

Whereas, this works. The difference is that I'm explicitlty defining the Search_Range, but I'd like to have the flexibility of the first example because then I won't have to define each range to search. The range to search is State_name!$A$4:$A$500.

`

 Function RateFetcher(Search_Range As Range, Plan_Text As String, Zip_Text As String, Sex As String, Tabacco_Use As String, Age As Integer, State As String)

Dim Plan As String
Plan = StrConv("Plan " + Plan_Text, vbLowerCase)

Dim ProperState As String
ProperState = StrConv(State, vbProperCase)

'Dim Search_Range As Range
'Search_Range = Worksheets(ProperState).Range("A4:A600")

Dim Location As String

For Each c In Search_Range
    If StrConv(c.Value, vbLowerCase) = Plan And c.Offset(1, 0) = Zip_Text Then
        Location = c.Address
    End If
Next c

Dim Sex_Tab_Offset As Integer

Dim Combined As String

Combined = Sex + Tabacco_Use

If Combined = "F NO" Then
    Sex_Tab_Offset = 1
ElseIf Combined = "M NO" Then
    Sex_Tab_Offset = 2
ElseIf Combined = "F YES" Then
    Sex_Tab_Offset = 3
Else
    Sex_Tab_Offset = 4
End If

Dim Age_Offset As Integer
Age_Offset = Age - 65

RateFetcher = Worksheets(ProperState).Range(Location).Offset(Age_Offset, Sex_Tab_Offset).Value

End Function `

First, try stepping through your code in the debugger to see where it's failing. Then see here: http://stackoverflow.com/questions/349613/what-does-the-keyword-set-actually-do-in-vba — jtolle, Jun 24 '11 at 14:30

score 3 · Accepted Answer · answered Jun 24 '11 at 15:03

You want to Set the reference to Range because Range is an object:

Dim Search_Range As Range
Set Search_Range = Worksheets(ProperState).Range("A4:A600")

Also, MsgBox expects an argument of type String, so you can't MsgBox a Range like that. You have to say what property of the Range you want to display, and I'm guessing you want to display its address:

MsgBox Search_Range.Address

Aa an aside, the default property of Range is Value, so MsgBox Search_Range would have worked if Search_Range were a reference to a single cell... but in your case, Search_Range.Value returns a 597-element Variantarray, and this can't be implicitly coerced to a String.

@tshauck Also note that you probably need an _EXIT FOR_ statement in your _FOR EACH_ loop. Otherwise, you will either unnecessarily run to the end of your range and/or overwrite the Location var where multiple conditions are met. — ray, Jun 24 '11 at 17:02

VBA Function returning #VALUE!

1 Answers1