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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to match up cells in different columns in Excel 2003
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)) -- Regards, Tom Ogilvy "LWhite" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to match up cells in different columns in Excel 2003
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to match up cells in different columns in Excel 2003
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 -- Regards, Tom Ogilvy "LWhite" wrote in message ups.com... 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 |
Reply |
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 |