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.

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.

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.

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.