Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
index/match type issue
I am creating an order page that has Columns A (Department), B (Part Number),
C (Quantity), and D (Need By Date). The Worksheet name is "Order". What I'm trying to do is retrieve the information in Column B and do an Index/Match type function on Worksheet "Cut List". The cut list page has a list of 1922 parts that our Laser cuts. The part number in row B on the Order page needs to search Column E on the Cut List page. Once the part numbers are matched I would like Column A's value in "Order" to be the new value in Column A on the "Cut List" page and so on. I need this process to continue on until all parts on the "Order" page have been accounted for. I'm looking in the books I have for something similar that I can adjust but I'm only finding how to copy ranges from one worksheet to the other. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
index/match type issue
Try this
Sub correctPN() With Sheets("Order") RowCount = 2 Do While .Range("B" & RowCount) < "" PartNo = .Range("B" & RowCount) With Sheets("Cut List") Set c = .Columns("E").Find(what:=PartNo, _ LookIn:=xlValues, lookat:=xlWhole) End With If c Is Nothing Then MsgBox ("Cannot find Part : " & PartNo) .Range("A" & RowCount) = "Cannot Find Dept" Else Dept = c.Value .Range("A" & RowCount) = Dept End If RowCount = RowCount + 1 Loop End With End Sub "Cerberus" wrote: I am creating an order page that has Columns A (Department), B (Part Number), C (Quantity), and D (Need By Date). The Worksheet name is "Order". What I'm trying to do is retrieve the information in Column B and do an Index/Match type function on Worksheet "Cut List". The cut list page has a list of 1922 parts that our Laser cuts. The part number in row B on the Order page needs to search Column E on the Cut List page. Once the part numbers are matched I would like Column A's value in "Order" to be the new value in Column A on the "Cut List" page and so on. I need this process to continue on until all parts on the "Order" page have been accounted for. I'm looking in the books I have for something similar that I can adjust but I'm only finding how to copy ranges from one worksheet to the other. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
index/match type issue
Thank you for your assistance Joel. I have a question about one part. What
is the LookIn:=xlValues, lookat:=xlWhole part referencing? "Joel" wrote: Try this Sub correctPN() With Sheets("Order") RowCount = 2 Do While .Range("B" & RowCount) < "" PartNo = .Range("B" & RowCount) With Sheets("Cut List") Set c = .Columns("E").Find(what:=PartNo, _ LookIn:=xlValues, lookat:=xlWhole) End With If c Is Nothing Then MsgBox ("Cannot find Part : " & PartNo) .Range("A" & RowCount) = "Cannot Find Dept" Else Dept = c.Value .Range("A" & RowCount) = Dept End If RowCount = RowCount + 1 Loop End With End Sub "Cerberus" wrote: I am creating an order page that has Columns A (Department), B (Part Number), C (Quantity), and D (Need By Date). The Worksheet name is "Order". What I'm trying to do is retrieve the information in Column B and do an Index/Match type function on Worksheet "Cut List". The cut list page has a list of 1922 parts that our Laser cuts. The part number in row B on the Order page needs to search Column E on the Cut List page. Once the part numbers are matched I would like Column A's value in "Order" to be the new value in Column A on the "Cut List" page and so on. I need this process to continue on until all parts on the "Order" page have been accounted for. I'm looking in the books I have for something similar that I can adjust but I'm only finding how to copy ranges from one worksheet to the other. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
index/match type issue
lookin is specifying you are look at the values in the cells. It could be
values, formuls, or comments. lookat can be either whole or part. "Cerberus" wrote: Thank you for your assistance Joel. I have a question about one part. What is the LookIn:=xlValues, lookat:=xlWhole part referencing? "Joel" wrote: Try this Sub correctPN() With Sheets("Order") RowCount = 2 Do While .Range("B" & RowCount) < "" PartNo = .Range("B" & RowCount) With Sheets("Cut List") Set c = .Columns("E").Find(what:=PartNo, _ LookIn:=xlValues, lookat:=xlWhole) End With If c Is Nothing Then MsgBox ("Cannot find Part : " & PartNo) .Range("A" & RowCount) = "Cannot Find Dept" Else Dept = c.Value .Range("A" & RowCount) = Dept End If RowCount = RowCount + 1 Loop End With End Sub "Cerberus" wrote: I am creating an order page that has Columns A (Department), B (Part Number), C (Quantity), and D (Need By Date). The Worksheet name is "Order". What I'm trying to do is retrieve the information in Column B and do an Index/Match type function on Worksheet "Cut List". The cut list page has a list of 1922 parts that our Laser cuts. The part number in row B on the Order page needs to search Column E on the Cut List page. Once the part numbers are matched I would like Column A's value in "Order" to be the new value in Column A on the "Cut List" page and so on. I need this process to continue on until all parts on the "Order" page have been accounted for. I'm looking in the books I have for something similar that I can adjust but I'm only finding how to copy ranges from one worksheet to the other. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A vlookup/index-match type problem | Excel Discussion (Misc queries) | |||
IF AND MATCH data type issue | Excel Worksheet Functions | |||
MATCH & INDEX ISSUE? | Excel Discussion (Misc queries) | |||
index match issue | Excel Worksheet Functions |