CODEWITHSUNDEEP
vbaexcel
z4k
z4k

Reputation: 617

passing range on another sheet to vlookup

I can't reference a range with a sheet for my function like I can with =vlookup.

This works: =MVLOOKUP(a2,B:C,2,",",", ")
This isn't: =MVLOOKUP(a2,Sheet3!B:C,2,",",", ")

The code:

Public Function MVLookup(Lookup_Values, Table_Array As Range, Col_Index_Num As Long, Input_Separator As String, Output_Separator As String) As String

 Dim in0, out0, i
 in0 = Split(Lookup_Values, Input_Separator)
 ReDim out0(UBound(in0, 1))

 For i = LBound(in0, 1) To UBound(in0, 1)  

  out0(i) = Application.WorksheetFunction.VLookup(in0(i), Table_Array, Col_Index_Num, False)

 Next i

 MVLookup = Join(out0, Output_Separator)

End Function

I don't know basic and I'm not planning to learn it, I rarely even use excel, so sorry for the lame question. I guess basic is really "basic" it took me 30 minutes to get to this point from the reference(reading included), but other 60 minutes in frustration because the above problem.
Help me so I can go back to my vba free life!

EDIT: Although the code above worked after an excel restart, Jeeped gave me a safer solution and more universal functionality. Thanks for that.

I was not planning to use it on other than strings but thanks for the addition, I wrongly assumed there is a check for data type every time and type passed along in the background and vlookup acting accordingly. I have also learned how to set default values to function input variables.

See solution.

Thanks again, Jeeped!

Upvotes: 1

Views: 163

Answers (2)

user4039065
user4039065

Reputation:

You are confusing 1 with "1" and regardless of your personal distaste for VBA, I really don't know of any programming language that treats them as identical values (with the possible exception of a worksheet's COUNTIF function).

Public Function MVLookup(Lookup_Values, table_Array As Range, col_Index_Num As Long, _
                         Optional Input_Separator As String = ",", _
                         Optional output_Separator As String = ", ") As String

    Dim in0 As Variant, out0 As Variant, i As Long

    in0 = Split(Lookup_Values, Input_Separator)
    ReDim out0(UBound(in0))

    For i = LBound(in0) To UBound(in0)
        If IsNumeric(in0(i)) Then
            If Not IsError(Application.Match(Val(in0(i)), Application.Index(table_Array, 0, 1), 0)) Then _
                out0(i) = Application.VLookup(Val(in0(i)), table_Array, col_Index_Num, False)
        Else
            If Not IsError(Application.Match(in0(i), Application.Index(table_Array, 0, 1), 0)) Then _
                out0(i) = Application.VLookup(in0(i), table_Array, col_Index_Num, False)
        End If

    Next i

    MVLookup = Join(out0, output_Separator)

End Function

When you Split a string into a variant array, you end up with an array of string elements. Granted, they look like numbers but they are not true numbers; merely textual representational facsimiles of true numbers. The VLOOKUP function does not treat them as numbers when the first column in your table_array parameter is filled with true numbers.

The IsNumeric function can reconize a string that looks like a number and then the Val function can convert that text-that-looks-like-a-number into a true number.

I've also added a quick check to ensure what you are looking for is actually there before you attempt to stuff the return value into an array.

Your split strings are one-dimensioned variant arrays. There is no need to supply the rank in the LBound / UBound functions.

    mvlookup_sample    mvlookup
            Sample data on Sheet3                                  Results from MVLOOKUP

Upvotes: 1

teylyn
teylyn

Reputation: 35915

This is not a valid range reference ANYWHERE in Excel B:Sheet3!C.

Either use B:C or Sheet3!B:C

Edit. Corrected as per Jeeped's comment.

Upvotes: 1

Related Questions