Selecting a range with a case statement....

[dan_l]

So, I have a VBA function:


Based on parameter 1, I want to select a range to match parameter 2.


function myfunction(parameter1 as string, parameter2 as string)

dim rMyRange as range

dim Match as string


Select Case Parameter1

case "A"

rMyRange = range("FirstRange")

case "B"

rMyRange = range("SecondRange")


myfunction = application.worksheetfunction.match(parameter2,rmyrange,0)


end function


But, it doesn't seem to work. I'm relative certain I have to do something with the ranges differently but I'm unsure what.


maybe rmyrange = range(firstrange").address or something?

 

[SirJB7]

Hi, dan_l!


Change your code to:

-----

[pre]

Function myfunction(parameter1 As String, parameter2 As String)
Dim rMyRange As Range
Dim Match As String

Select Case parameter1
Case "A"
Set rMyRange = Range("FirstRange")
Case "B"
Set rMyRange = Range("SecondRange")
End Select

myfunction = Application.WorksheetFunction.Match(parameter2, rMyRange, 0)

End Function

[/pre]
-----


There were missing the two Set keywords and the End Select instruction.

It's always a good practice to check the code before running it with Alt-D L (Debug, Compile) from the VBA editor.


Regards!