vba for vlookup to return multiple columns

[ThomasOES]

Hello,
I would like to use VLookup to match the name entered in a userform dialog box to a name in Sheet("CRM_Lib") Column A. Then return the data that is ~20 columns wide from Sheet("CRM_Lib") to Sheet("Header") Cell ("Y42").

For instance the userform asks for a part #. I enter it, then the data for the part (contained in Sheet "CRM_Lib") is placed is Sheet "Header" , Starting at cell "Y42".

Heres an example of what I'm trying

The top row is chemical elements
The next row is the % of a part #.
I currently use a userform to enter a part # in the same row 3 columns to the left and then pastes a vloolup formula across the cells, but I'd like vba to do it with code.

[TABLE="width: 1856"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Al[/TD]
[TD="width: 64, bgcolor: transparent"]As[/TD]
[TD="width: 64, bgcolor: transparent"]B![/TD]
[TD="width: 64, bgcolor: transparent"]Be[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]Ca![/TD]
[TD="width: 64, bgcolor: transparent"]Co[/TD]
[TD="width: 64, bgcolor: transparent"]Cr[/TD]
[TD="width: 64, bgcolor: transparent"]Cu[/TD]
[TD="width: 64, bgcolor: transparent"]Fe[/TD]
[TD="width: 64, bgcolor: transparent"]La[/TD]
[TD="width: 64, bgcolor: transparent"]Mg![/TD]
[TD="width: 64, bgcolor: transparent"]Mn[/TD]
[TD="width: 64, bgcolor: transparent"]Mo[/TD]
[TD="width: 64, bgcolor: transparent"]N![/TD]
[TD="width: 64, bgcolor: transparent"]Nb[/TD]
[TD="width: 64, bgcolor: transparent"]Ni[/TD]
[TD="width: 64, bgcolor: transparent"]P[/TD]
[TD="width: 64, bgcolor: transparent"]Pb[/TD]
[TD="width: 64, bgcolor: transparent"]S[/TD]
[TD="width: 64, bgcolor: transparent"]Sb[/TD]
[TD="width: 64, bgcolor: transparent"]Si[/TD]
[TD="width: 64, bgcolor: transparent"]Sn[/TD]
[TD="width: 64, bgcolor: transparent"]Ta[/TD]
[TD="width: 64, bgcolor: transparent"]Ti[/TD]
[TD="width: 64, bgcolor: transparent"]V[/TD]
[TD="width: 64, bgcolor: transparent"]W[/TD]
[TD="width: 64, bgcolor: transparent"]Zn[/TD]
[TD="width: 64, bgcolor: transparent"]Zr[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](<0.002)[/TD]
[TD="bgcolor: transparent"]-1[/TD]
[TD="bgcolor: transparent"]-1[/TD]
[TD="bgcolor: transparent"]-1[/TD]
[TD="bgcolor: transparent"]1.06[/TD]
[TD="bgcolor: transparent"]-1[/TD]
[TD="bgcolor: transparent"]0.047[/TD]
[TD="bgcolor: transparent"]16.87[/TD]
[TD="bgcolor: transparent"]0.09[/TD]
[TD="bgcolor: transparent"]-1[/TD]
[TD="bgcolor: transparent"]-1[/TD]
[TD="bgcolor: transparent"]-1[/TD]
[TD="bgcolor: transparent"]1.15[/TD]
[TD="bgcolor: transparent"]0.5[/TD]
[TD="bgcolor: transparent"]406[/TD]
[TD="bgcolor: transparent"]0.005[/TD]
[TD="bgcolor: transparent"]0.35[/TD]
[TD="bgcolor: transparent"]0.022[/TD]
[TD="bgcolor: transparent"]-1[/TD]
[TD="bgcolor: transparent"]0.007[/TD]
[TD="bgcolor: transparent"]-1[/TD]
[TD="bgcolor: transparent"]0.47[/TD]
[TD="bgcolor: transparent"](0.004)[/TD]
[TD="bgcolor: transparent"]-1[/TD]
[TD="bgcolor: transparent"]0.001[/TD]
[TD="bgcolor: transparent"]0.13[/TD]
[TD="bgcolor: transparent"]0.11[/TD]
[TD="bgcolor: transparent"]-1[/TD]
[TD="bgcolor: transparent"]-1[/TD]
[/TR]
</tbody>[/TABLE]

Currently I try

Range("Y42") = Application.WorksheetFunction.VLookup _
(Range("V42").Value, Sheets("CRM_Lib").Range("A2:AD2001"), 2, False)

The userform puts the part # in "V42". But I only get the data from the second column. In this case (<0.002). (The part # is the column 1). How to get the whole row?

Thanks

Tom

 

[ask2tsp]

hello ThomasOES.

Please paste the following code in the code page of the Header sheet. To get there right-click the Header sheet's label and select View code.

Sub Worksheet_Change(ByVal Target As Range)
'paste this sub in the code page of the Header sheet
    Dim lastColumn As Long, lastLabel As Long
    Dim matchedRow As Long, lastRow As Long
    
    If Target.Address(0, 0) = "V42" Then 'only act if cell V42 changes
        With ThisWorkbook.Worksheets("CRM_lib")
            lastRow = .Range("A2").End(xlDown).Row
            matchedRow = 3
            While .Cells(matchedRow, 1).Value <> Target.Value _
              And matchedRow <= lastRow
                matchedRow = matchedRow + 1
            Wend
            Range(Range("Y42"), Range("Z42")).Clear
            If matchedRow <= lastRow Then
                lastColumn = .Cells(matchedRow, 1000).End(xlToLeft).Column
                Range(Range("Y42"), Cells(42, Columns("Y").Column + lastColumn)).Value = _
                .Range(.Cells(matchedRow, 2), .Cells(matchedRow, lastColumn)).Value
            End If
        End With
    End If
End Sub

This code runs every time something on the sheet changes. Since it starts with checking if the change occurred in cell V42, only changes in that cell's value will trigger the lookup like action.