Index on another worksheet

  • Thread starter Thread starter Cerberus
  • Start date Start date
C

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.
 
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.
 
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).
 
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.
 
P.S. If the user does not leave the cursor on the entry row while the
"Submit" button is clicked, the code fails.
 
Thank you so very much.

JLGWhiz said:
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.
Click to expand...
 
Back
Top