clever look up please !

Paula F

New Member
Joined
Nov 10, 2005
Messages
17
I'll try to explain what I need.......
I plan a production line. Each product has a set of routers (stages) that need to happen in order to make it, eg dispensary, filling, packaging, micro testing and then shipping.
I have all the products to be made in a month on an excel sheet, in a column (all the part numbers). I will then have all part numbers with their respective router list in another file. I need to have some sort of look up which gives me (on another sheet) the part number on the plan and then the list of routers underneath it, and then the next part number on the plan with it's routers and so on. I can only do basic VLOOKUPS which look for one reference and then report back what is in another column in the same row. How do you say "look for this part number and tell me what is in the (for eg) 8 rows beneath it ?
thanks
Paula
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

asgourlay

New Member
Joined
Jul 13, 2005
Messages
35
Possible answer

If you are looking at having the anwsers to the lookup appearing in the same cell please see the attached code.

Place code into the code sheet of the sheet where your partnumbers are listed and change the variables to accommodate. I had "Part Numbers" in column A on a sheet called SHEET 1 referred as "activesheet" and a list of part numbers and routers on SHEET 2 in columns A and B respectively

Hope this helps

Aaron

:oops:

_______________________________________________________________________

Public Sub newmacro()
Dim N_1 As Integer ' USED TO INCREMENT THE ROW NUMBER ON SHEET 2
Dim N_2 As Integer ' USED TO IDENTIFY FIRST RUN OF CODE FOR ITEMS ON SHEET 1
Dim VAL As Variant ' LOOKUP VALUE
Dim VAR As Variant ' ANSWER
Dim X_VAR As Variant ' CUMULATIVE ANSWER
N_2 = 0
While ACTIVESHEETS.Cells(X_ROW, "A") <> 0
N_1 = 1
VAL = ActiveSheet.Cells(X_ROW, "A")
While Sheets("SHEET 2").Cells(N_1, "A") <> 0
If VAL = Sheets("SHEET 2").Cells(N_1, "A") Then
VAR = Sheets("SHEET 2").Cells(N_1, "B")
If N_2 = 0 Then
ActiveSheet.Cells(X_ROW, "B") = VAR
Else
X_VAR = ActiveSheet.Cells(X_ROW, "B") & ", " & VAR
ActiveSheet.Cells(X_ROW, "B") = X_VAR
End If
N_2 = N_2 + 1
End If
N_1 = N_1 + 1
Wend
N_2 = 0
X_ROW = X_ROW + 1
Wend

End Sub
 

asgourlay

New Member
Joined
Jul 13, 2005
Messages
35
Ignore previous posts

Sorry but I messed up the code a little please see attached

Public Sub newmacro()
Dim N_1 As Integer ' USED TO INCREMENT THE ROW NUMBER ON SHEET 2
Dim N_2 As Integer ' USED TO IDENTIFY FIRST RUN OF CODE FOR ITEMS ON SHEET 1
Dim VAL As Variant ' LOOKUP VALUE
Dim VAR As Variant ' ANSWER
Dim X_VAR As Variant ' CUMULATIVE ANSWER
Dim X_ROW As Integer

N_2 = 0
X_ROW = 1
While ActiveSheet.Cells(X_ROW, "A") <> 0
N_1 = 1
VAL = ActiveSheet.Cells(X_ROW, "A")
While Sheets("SHEET 2").Cells(N_1, "A") <> 0
If VAL = Sheets("SHEET 2").Cells(N_1, "A") Then
VAR = Sheets("SHEET 2").Cells(N_1, "B")
If N_2 = 0 Then
ActiveSheet.Cells(X_ROW, "B") = VAR
Else
X_VAR = ActiveSheet.Cells(X_ROW, "B") & ", " & VAR
ActiveSheet.Cells(X_ROW, "B") = X_VAR
End If
N_2 = N_2 + 1
End If
N_1 = N_1 + 1
Wend
N_2 = 0
X_ROW = X_ROW + 1
Wend

End Sub

My apologies

Aaron
 

Paula F

New Member
Joined
Nov 10, 2005
Messages
17
Hi Aaron
thank you very much for your help. Before I try to do this (I am not very good with all this code !), would you be able to tell me how I could get it so that each router is put into its own cell ? eg I would have a part number in one row, then the next however many rows would have the routers in them... then it would be the next part number and then all the routers for that. The reason they have to be in separate rows is because I then need to paste them into Microsoft Project, and it will only take things if they are in separate rows.
thank you again
Paula
 

Paula F

New Member
Joined
Nov 10, 2005
Messages
17

ADVERTISEMENT

Aaron
I put in the code and ran it and it said "subscript out of range"
what does that mean ?

thanks


Paula
 

asgourlay

New Member
Joined
Jul 13, 2005
Messages
35
Hi Paula

Subscript out of range usually refers to sheet names (run-time error 9).

If you change the SHEET 2 in the code to the sheet where your list of parts and routers is it should be ok.

Aaron
 

Watch MrExcel Video

Forum statistics

Threads
1,118,455
Messages
5,572,216
Members
412,448
Latest member
ManuW
Top