CODEWITHSUNDEEP
excelvba
BuZz
BuZz

Reputation: 17445

Trying to return a range as function output, get type mismatch?

The following function returns a "type mismatch". I don't understand, as I paid attention to using the "Set" instruction to return my resulting range.

I debugged the function, I get a proper range to return, so the problem is elsewhere.. Hmmmm...

Function getVals(column As String) As Range
    Dim col As Variant
    col = Application.Match(column, ThisWorkbook.ActiveSheet.Range("1:1"), 0)
    Dim rng As Range
    Set rng = ThisWorkbook.ActiveSheet.Cells(1, col)
    Set rng = rng.Offset(1, 0)
    Set rng = Range(rng, rng.End(xlDown))
    Set getVals = rng
End Function

Thanks in advance guys for any help :)

UPDATE : I am looking at how to send my results as an array. I tried combinations of the function returning "variant"/"variant()" type, and passing rng.value2 as result, but no success.

Upvotes: 1

Views: 7872

Answers (4)

mischab1
mischab1

Reputation: 1601

To return your results as an array of values, simply change the return type to Variant and return rng.Value. The below code works for me as long as the passed column string exists in ThisWorkbook.ActiveSheet.Range("1:1").

Function getVals(column As String) As Variant
    Dim col As Variant
    col = Application.Match(column, ThisWorkbook.ActiveSheet.Range("1:1"), 0)
    Dim rng As Range
    Set rng = ThisWorkbook.ActiveSheet.Cells(1, col)
    Set rng = rng.Offset(1, 0)
    Set rng = Range(rng, rng.End(xlDown))
    getVals = rng.Value
End Function

Sub TestingGetVals()
   Dim v As Variant, i As Integer
   v = getVals("a")  ' returns a 2-D array
   For i = 1 To UBound(v)
      Debug.Print v(i, 1)
   Next i
End Sub

Upvotes: 1

mischab1
mischab1

Reputation: 1601

Other people are faster at writing then I am. :-) There is one other possibility that hasn't been mentioned yet.

Since you didn't get the error when debugging, the issue might be your use of ActiveSheet. If the wrong worksheet is active then Match will cause the error as stated by the other answers.

If you are explicit, does the error go away?

col = Application.Match(column, ThisWorkbook.Sheet(1).Range("1:1"), 0)

Upvotes: 0

Tony Dallimore
Tony Dallimore

Reputation: 12413

Firstly I do not understand what you are doing. You have a parameter column but you are searching for a cell within row 1 that contains that value. For example, if column = 23, and P1 contains 23, Match should return 16.

Your routine fails because if the Match fails, col is set to Error 2042. You should test col before using it as a number.

In my test I set row 1 to numbers in a random sequence. My Match failed because cell P1 contained number 23 but variable column contains string "23". When I reclassified column as Long, the Match worked.

I am unhappy with Siddharth's use of On Error. I do not like to use On Error for errors I expect. I would test col to be numeric after the Match.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149297

You are getting that error because Match is not able to find what you want and hence your rng is evaluating to "nothing" :)

Consider this code

Option Explicit

Sub Sample()
     Dim Ret As Range

     If Not getVals("Value To Match") Is Nothing Then
        Set Ret = getVals("Value To Match")
        MsgBox Ret.Address
     Else
        MsgBox "Value To Match - Not Found"
     End If
End Sub

Function getVals(column As String) As Range
    Dim col As Variant
    Dim rng As Range

    On Error GoTo Whoa

    col = Application.Match(column, ThisWorkbook.ActiveSheet.Range("1:1"), 0)

    Set rng = ThisWorkbook.ActiveSheet.Cells(1, col)

    Set rng = rng.Offset(1, 0)
    Set rng = Range(rng, rng.End(xlDown))
    Set getVals = rng

    Exit Function
Whoa:
    Set getVals = Nothing
End Function

Upvotes: 0

Related Questions