Hello,

I am building a spreadsheet to assist my staff in quoting. The workbook
has three sheets. The quote worksheet where they select the materials
and tasks for the jobs; an imported database table from MSSQL and
finally a import sheet that is set up to follow the rules for bringing
the information into my MRP program.

The way that I want to have this run goes like this. The quoter uses a
drop down box to select the material type. This then goes to the
database and brings up all the availble parts of that type into a
second combo box. It searches column A on the database sheet by part
number. The second combo box displays the available materials
description which is on the database sheet column B.

Now here is where I have ended up stuck. I want to press a button and
copy the part number, unit of measure that we use it in, and the part
description into the next available line of the quote worksheet. I
Don't know how to have the macro attached to the button search and then
copy over the correct row information from the database sheet.

As sample data: column A on the datbase sheet contains part numbers:
CA1
CA2
CF2
CF2
CB1
CB2
AS1
AS2

Column B is the part rev:
rel
rel
rel
rel
rel
B
001
001

Column C is descriptions:
glue 1
glue 2
cloth 1
cloth 2
bag 1
bag 2
Assembly 1
Assembly 2

Column D is the unit of use:
large cup
small cup
square foot
square foot
linear yard
linear yard
each
each

The code I am currently using is as follows;

Private Sub ComboBox1_Change()

Dim myRng As Range
Dim myCell As Range
Dim myPfx As String

With Worksheets("SQL")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

Me.ComboBox2.Clear

If Me.ComboBox1.ListIndex < 0 Then
'do nothing
Else
Select Case Me.ComboBox1.ListIndex
Case Is = 0 'All Parts
myPfx = "*"
Case Is = 1 'Assembly
myPfx = "as*"
Case Is = 2 'Adhesives
myPfx = "ca*"
Case Is = 3 'Bagging
myPfx = "cb*"
Case Is = 4 'Fabric
myPfx = "cf*"
Case Else
myPfx = "*" 'just in case
End Select
End If

For Each myCell In myRng.Cells

If LCase(myCell.Value) Like LCase(myPfx) Then
Me.ComboBox2.AddItem myCell.Offset(0, 3)
End If

Next myCell
'

End Sub

Like I said, after clicking on the first combo box to select the
material based on part number I go to the second one and select the
actual item I want to include. I would then like to click on a button
and paste into the next emtpy line of my quote worksheet the
appropriate cells for that part number. I am lost as to how to grab and
move those cells based on that part number.

Thank you in advance for any help.
LWhite