To find a row from a vlookup

[alexaronson]

Hello and thanks for looking at my two part question.

I am consolidating several data sources into a single table for a tool that I am building.

As of right now, I have 36 vba vlookups to build my table. In many instances, I have to hit the same sheet and row to get the data I need to fill out my table. I want to see if there is a way for me to eliminate 32 of my vlookups.

Here is an example of my code.

    'Avg Traited Points of Distribution
    On Error Resume Next
    x = WorksheetFunction.VLookup(tpmWS.Cells(i, j), yagWS.Range("A1:F" & LastRow5), 6, False)
    If Err.Number = 0 Then tpmWS.Cells(i, j + 27) = x
    'POS Store Count
    On Error Resume Next
    x = WorksheetFunction.VLookup(tpmWS.Cells(i, j), yagWS.Range("A1:G" & LastRow5), 7, False)
    If Err.Number = 0 Then tpmWS.Cells(i, j + 28) = x

As you can see, in this example I have two Vlookups going to the same table (yagWS), but only pulling one cell each.

Question 1) How can I use a vlookup to just the find the row that the data is on?

If I know the row (lets call it "y"), I can pull the second data set without the vlookup by using the following as well as other data points on that row.

tpmWS.Cells(i,j+28) = yagWS.Cells(y,7)
tpmWS.Cells(i,j+29) = yagWS.Cells(y,8)
tpmWS.Cells(i,j+30) = yagWS.Cells(y,9)

Question 2) How could I find a row, then pull a range of data based upon that row from yagWS into tpmWS in a single transaction with out using range(blah, blah).Copy

Thanks for looking at my questions

 

[Andrew Poulsom]

You can use the Match WorksheetFunction to return the row index of the item within the lookup range.

 

[MrKowz]

Couple of things I'd reccomend. First, declare the variable X as a variant. That way we can test for the error X instead of using an On Error statement. Also, switch up your formula to Application.Match.

Dim x As Variant
x = Application.Match(tpmws.Cells(i, j), yagWS.Range("A1:A" & LastRow5), 0)
If IsError(x) Then
    ' Code when x is an error
Else
    ' Code when x is not an error
End If

 

[alexaronson]

Wow this is excellent.

So the code provides x as the value of the row where the data resides.

That is cool.

With the IsError line I can create a kickout report. That is awsome!

Thanks for the help yall!