Index on another worksheet
Thank you so very much.
"JLGWhiz" wrote:
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.
"Cerberus" wrote in message
...
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" wrote:
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" wrote in message
...
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.
|