look to do an Offset of something like a offset

[B~O~B]

I am look to do an Offset of something like a offset. I level data in
column "A" and Part Number in Column "B". What I need to do is look
at the level data for "1, 2, 3, 4, ets." Then lookup the table for the
first value that is equal to data -1 and report out the Part Number
from its column "B".

Data
A B
1 P1
2 P2
3 P3
3 P4
4 P5

Results
A B C
0 P
1 P1 P
2 P2 P1
3 P3 P2
3 P4 P2
4 P5 P4

 

[RyanH]

I am assuming you have one worksheet with the data which I have named "Data"
and another worksheet with results which I have named "Results". This sub
will look in the "Data" sheet for each level from "Results" and place the
part number in Col.B in the "Results" sheet. You can change the names to fit
your application.

Option Explicit

Sub LookUpParts()

Dim lngLastRow As Long
Dim rngResults As Range
Dim cell As Range

lngLastRow = Sheets("Results").Cells(Rows.Count, "A").End(xlUp).Row
Set rngResults = Sheets("Results").Range("A1:A" & lngLastRow)

For Each cell In rngResults
On Error Resume Next
cell.Offset(0, 1) = WorksheetFunction.VLookup(cell.Value,
Sheets("Data").Range("A:B"), 2, False)
On Error GoTo 0
Next cell

End Sub

Hope this helps! If so, please click "YES" below.

 

[B~O~B]

I am assuming you have one worksheet with the data which I have named "Data"
and another worksheet with results which I have named "Results".  This sub
will look in the "Data" sheet for each level from "Results" and place the
part number in Col.B in the "Results" sheet.  You can change the names to fit
your application.

Option Explicit

Sub LookUpParts()

Dim lngLastRow As Long
Dim rngResults As Range
Dim cell As Range

    lngLastRow = Sheets("Results").Cells(Rows.Count, "A").End(xlUp)..Row
    Set rngResults = Sheets("Results").Range("A1:A" & lngLastRow)

    For Each cell In rngResults
        On Error Resume Next
        cell.Offset(0, 1) = WorksheetFunction.VLookup(cell.Value,
Sheets("Data").Range("A:B"), 2, False)
        On Error GoTo 0
    Next cell

End Sub

Hope this helps!  If so, please click "YES" below.
--
Cheers,
Ryan







- Show quoted text -

Ryan,

Thanks for the help, but, I was not able to get it working. I
generated the two sheet and added the data i ran it... It did
nothing...