how to match up cells in different columns in Excel 2003

[LWhite]

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

 

[Tom Ogilvy]

Make combobox2 have a two column list. The second column can be hidden for
purposes of display, this second column will hold the row on the
spreadsheet of that item in the combobox. then you just use that value to
locate the item.

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
Me.ComboBox2.Columncount = 2
Me.Combobox2.ColumnWidths = .75 in;0
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)
Me.Combobox2.List(Me.combobox2.Listcount -1,1) = mycell.row
End If

Next myCell
'

End Sub

then to get the row

rw = clng(Me.combobox2.List(me.combobox2.ListIndex,1))

 

[LWhite]

Thank you very much Mr. Oglivy. I appreciate your help but am not
experienced enough to know what comes next with this.

Ok, I see how this gives me the row but how do I put it into the cells
I want to paste? Meaning that if I select CF2 which is row three, how
do I then grab the value of D3 and paste it into a row? Should I be
setting the rw you show at the bottom of your example as 'dim rw as
(something)' and working with it as a memory item?

Could you provide this as an example:

ON click
take rw from combo box 2
select cell d(rw)
contents copy
select sheet quote
select cell (A17)
paste


I don't understand how to take the rw and then make my selection from
it. Also, since the row is what I will have available do I need to
count over from the first cell in the row or can I enter it as a letter
callout?

Thank you for your help and patience.
LWhite

 

[Tom Ogilvy]

Private Sub Combobox2_Click()
Dim rw as Long
With Combobox2
rw = clng(.List(.ListIndex,1))
End with
Worksheets("Data").cells(rw,"D") _
.copy Destination:= _
worksheets("Quote").Range("A17")
End Sub