Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to match up cells in different columns in Excel 2003
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match 2 Columns, Return 3rd, Differing Match Types | Excel Worksheet Functions | |||
Match formula to match values in multiple columns | Excel Discussion (Misc queries) | |||
Compare cells and copy columns after match | Excel Worksheet Functions | |||
Variables in Excel (Cells & columns) - Excel 2003 | Excel Discussion (Misc queries) | |||
excel 2003: how to match records of 2 tables according to 2 columns? | Excel Worksheet Functions |