Index on another worksheet

[Cerberus]

I'm trying to create a purchase order system. What I would like to do is
page one titled "Order" I have 3 columns; A (Department), B (Part Number),
and C (Quantity). Once the person fills out the required info they will
click on a "submit" form control button. What I would like to have happen
next is, the part number inserted in column B will index the same part number
in column B on the page two titled "Cut List".

I would like a message box to come up with the information from columns F
and G of the same row that was indexed earlier. Column F (Order Quantity),
column G (Need By Date). If F and G are blank I want "Cut List" column E to
have today's date, "Cut List" column F, the information from "Order" column
C, and so on. If there is information in Column F and G, I need then to have
the option of adding the quantity of parts to the existing Need By Date or
creating a new need by date. If a new date is required the information from
"Order" would be put into "Cut List" column J-L.

For the most part, I really need to know how to index a cell from one
worksheet and match the same part number on another worksheet and make that
row active. I could probably figure out the rest but thought if anyone has
done something similar I could just use your idea.

Thanks in advance for any help or ideas you may provide.

 

[JLGWhiz]

the part number inserted in column B will index the same part number
in column B on the page two titled "Cut List".

What does the above statement mean? Does it mean to find a matching part
number? or Does it mean to add to a list and then sort the list? INDEX is a
function of Excel that can be used to return values from arrays or to return
references. You need to clarify the statement.

 

[Cerberus]

Sorry about the vague information or improper use of terms. I wish I knew
the proper terms so I could utilize the books I have more effectivly. I want
it to match the same part number from a list of 2066 parts located on page
two (Cut List).

 

[JLGWhiz]

This assumes that Columns F and G are on the second sheet.

Private Sub CommandButton1_Click()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
Dim c As Range
Set sh1 = Sheets("Order")
Set sh2 = Sheets("Cut List")
lr = sh2.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh2.Range("B2:B" & lr)
Set c = rng.Find(sh1.Range("B" & ActiveCell.Row).Value, LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox "Col F Value is: " & sh2.Range("F" & c.Row) & vbCrLf _
& "Col G value is: " & sh2.Range("G" & c.Row)
End If
End Sub

I did not address the need date change because there is no criteria to
establish when that decision would be needed. This code uses a command
button from the Control Toolbox, with the button being installed on the
first sheet "Order". The code goes in the sheet code module.

 

[JLGWhiz]

P.S. If the user does not leave the cursor on the entry row while the
"Submit" button is clicked, the code fails.

 

[Cerberus]

Thank you so very much.

This assumes that Columns F and G are on the second sheet.

Private Sub CommandButton1_Click()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
Dim c As Range
Set sh1 = Sheets("Order")
Set sh2 = Sheets("Cut List")
lr = sh2.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh2.Range("B2:B" & lr)
Set c = rng.Find(sh1.Range("B" & ActiveCell.Row).Value, LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox "Col F Value is: " & sh2.Range("F" & c.Row) & vbCrLf _
& "Col G value is: " & sh2.Range("G" & c.Row)
End If
End Sub

I did not address the need date change because there is no criteria to
establish when that decision would be needed. This code uses a command
button from the Control Toolbox, with the button being installed on the
first sheet "Order". The code goes in the sheet code module.