Using Concatenated Vlookup in VBA-CodePudding

I can't seem to figure out how to concatenate two cell inputs (myvalue = Cell D2 and myvalue2 = Cell C3; the cells can change hence the input function) in to an application.vlookup vba.

Online it says you can't use the concatenate function with application.worksheetfunction.vlookup so trying to figure out an alternative. Any thoughts?

Dim myValue As String
Dim myValue2 As String

myValue = InputBox("Please input Cell that first merch cat is in")
myValue2 = InputBox("Please input Cell that first site is in")
    
ActiveCell = Application.VLookup([myValue2] & [myValue], ActiveSheet.Range("BA:BE"), 5, False)

CodePudding user response：

thank you for the assistance. Went away and looked at it from another angle and now works exactly how I wanted it to:

Dim myValue As Range

Set myValue = Application.InputBox(prompt:="Please input Cell that first merch cat is in", Type:=8)
Set myValue2 = Application.InputBox(prompt:="Please input Cell that first site is in", Type:=8)

 ActiveCell = Application.VLookup([myValue2] & [myValue], ActiveSheet.Range("BA:BE"), 5, False)

CodePudding user response：

With this setup

and this code

Sub test()
    
    Dim valone As String
    Dim valtwo As String
    
    valone = InputBox("Enter one")
    valtwo = InputBox("Enter two")
    
    ActiveCell.Value = Application.VLookup(valone & valtwo, ActiveSheet.Range("BA:BE"), 5, False)
    
End Sub

If I type 470 in the first prompt and 11516 in the second prompt, I get A1 in the active cell. Note the green triangles in BA. I entered those numbers with a leading apostrophe so they would be considered text and not numeric. With this next setup, there are no triangles because BA contains numbers.

In that case, the code returns #N/A because it can't find the text "47011516" in the list of numbers. You could change your code to convert the input box results to a number using the Val() function.

Sub test()
    
    Dim valone As String
    Dim valtwo As String
    
    valone = InputBox("Enter one")
    valtwo = InputBox("Enter two")
    
    ActiveCell.Value = Application.VLookup(Val(valone & valtwo), ActiveSheet.Range("BA:BE"), 5, False)
    
End Sub

And that will work. Another option is to use Find instead of a function. I would write it like this

Sub test()
    
    Dim valone As String
    Dim valtwo As String
    Dim rFind As Range
    
    valone = InputBox("Enter one")
    valtwo = InputBox("Enter two")
    
    Set rFind = ActiveSheet.Range("BA:BA").Find(valone & valtwo, , xlValues, xlWhole)
    
    If Not rFind Is Nothing Then
        ActiveCell.Value = rFind.Offset(0, 4).Value
    Else
        ActiveCell.Value = "not found"
    End If
    
End Sub

That won't care if it's a number or text.

CodePudding user response：

Please, try understanding an important issue about Vlookup:

lookup_value and first column of table_array must be identically formatted! to avoid #N/A...

I mean, if the value in "BA:BA" is a number and formatted as General, you should supply a numeric value as lookup_value:

ActiveCell = Application.VLookup(CDbl(myValue2 & myValue), ActiveSheet.Range("BA:BE"), 5, False)

If it is formatted as Text, it should work in this way:

ActiveCell = Application.VLookup(CStr(myValue2 & myValue), ActiveSheet.Range("BA:BE"), 5, False)

In this second case, converting to string of two concatenated strings is just in case. Theoretically, it is not necessary...