look to do an Offset of something like a offset
 

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

look to do an Offset of something like a offset
 

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


"B~O~B" wrote:

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

look to do an Offset of something like a offset
 

On Nov 10, 11:01*pm, RyanH wrote:
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



"B~O~B" wrote:
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- Hide quoted text -

- 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...