Loop through named range and copy info into a combo box

**Patronedheart** · 12-19-2009, 04:53 PM

I have a complicated question.
I have a sheet with a list of parts and serial numbers. The parts fall into different categories, ie: electronics, batteries, interconnects to name a few.
I have another sheet that will track the hours on these parts. I can only select one unique part from each category on this sheet. These parts combine to build a larger unit, and you only track hours when the unit is asembled. I wanted to have a dropdown combo box beside each part category for that category's individual part serial number. So far, I've been able to dynamically populate the combo box with all of the serial numbers on the other sheet, but I don't want the user to have to scroll through unnecessary serial numbers, only the ones for that part category. I did this by creating a dynamic range for the name and filling it with all of the serial numbers then asigning a name to the range, and using the name in the format control of the combo box. I can't seem to figure out how to add a conditional statement to the format control to only fill the combo box with serial numbers for that part category. I'm sure it involves a loop of the rows to only find parts with that name, and then loop through that to display only the serial numbers.
So, to put it simply, i want the combo box to only show the serial numbers for that part category.
Here's how the sheet would look:
Electronics - Q183 V
Interconnect - Q84 V
Battery - Q56 V
*the "V" is the dropdown arrow.
Hopefully that makes sense. If not, let me know, and I'll expound.
I also have another question about the hours, but I'll save it until I can get the combo boxes filled with the right serial numbers.
Thanks in advance.

Kevin

**Palmetto** · 12-19-2009, 05:40 PM

Why not use Data Validation with the list option using dependent lists? It provides a drop down selection like the list box.

See attached. If this solution is not viable for you, post a sample workbook that duplicates your exact structure and manually showed the desired results.

Additional to the data validation drop down, you could use a bit of VBA code the clear the serial number selection so that a fresh selection is always made when selecting a new category.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Range("B2").ClearContents
    End If

End Sub

**Patronedheart** · 12-20-2009, 12:38 AM

Here's a sample of my workbook. The inventory on the first sheet needs to fill the combo box on the 2nd sheet depending on the part's serial number.
I only have the first 2 module's combo boxes for demonstration purposes.
Let me know if you need any futher info.
Thanks,
Kevin

Originally Posted by Palmetto

Why not use Data Validation with the list option using dependent lists? It provides a drop down selection like the list box.

See attached. If this solution is not viable for you, post a sample workbook that duplicates your exact structure and manually showed the desired results.

Additional to the data validation drop down, you could use a bit of VBA code the clear the serial number selection so that a fresh selection is always made when selecting a new category.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Range("B2").ClearContents
    End If

End Sub

**Palmetto** · 12-21-2009, 07:25 AM

First. . . .

Avoid (like the plague) the use of merged cells on data sheets. I've removed them from the Inventory sheet and reformatted (retaining the same look). Merged cells often cause issues with VBA.

Changes made in the attachment:

Deleted the combo boxes on the Hours sheet
Implemented Data Validation, List option as means to select the serial number
Added code, below, automating Advanced Filter to extract serial numbers and create named ranges for use in the Data Validation lists.

The code needs to be further developed to include all categories and you can easily see the pattern of what needs to be done.

Note: I opted to manually set up the criteria cells for the Advanced Filter. See rows 1:2 on the Inventory Sheet, beginning with column-I.

Option Explicit

Sub Create_SN_Lists()

    Dim lrow As Long
    
    lrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    With Sheet2
        'clear the cells to received updated serial numbers
        .Range("I3:Z" & lrow).ClearContents
        
        'extract the serial numbers for the Spearpoint category
        .Range("A3:B" & lrow).AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Sheet2.Range("I1:I2"), CopyToRange:=Sheet2.Range("I3"), Unique:=False
            
         'delete the unneeded cells so as to retain only the serial numbers
        .Range("I3:I" & lrow).Delete shift:=xlToLeft
        
        
        'extract the serial numbers for the Electronics category
        .Range("A3:B" & lrow).AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Sheet2.Range("K1:K2"), CopyToRange:=Sheet2.Range("K3"), Unique:=False
        
        'delete the unneeded cells so as to retain only the serial numbers
        .Range("K3:K" & lrow).Delete shift:=xlToLeft
    
    End With

    'Add the named ranges to the workbook for each category
    With ThisWorkbook
        .Names.Add Name:="Spearpoint", RefersToR1C1:="=OFFSET(inventory!R4C9,0,0,COUNTA(inventory!C9),1)"
        .Names.Add Name:="Electronics", RefersToR1C1:="=OFFSET(inventory!R4C11,0,0,COUNTA(inventory!C11),1)"
    
    End With
        
    Application.ScreenUpdating = True

End Sub

**Patronedheart** · 12-22-2009, 12:25 AM

Thanks, Palmetto. I'll start working on it tomorrow and let you know how it goes.
My whole workbook is unfortunately full of merged cells. How the heck do you get around it if your format calls for expanded cells?

**Patronedheart** · 12-25-2009, 06:50 PM

Ok, I modified the code, and it works perfectly with what I need it to do. The only thing I changed was to have it automatically assign the range to the name when you deactivate the sheet. I did that because I don't want to have my users click on something they will probably not understand.
I do have a question, though. I like to know how the code is working, so I was wondering what was going on with the following lines of code:

.Range("A3:B" & lrow).AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Sheet2.Range("T1:T2"), CopyToRange:=Sheet2.Range("T3"), Unique:=False

I realize this is used to define the range and assign a name to it, but not sure how it's accomplishing that.

also,

.Names.Add Name:="Spearpoint", RefersToR1C1:="=OFFSET(inventory!R4C9,0,0,COUNTA(inventory!C9),1)"

specifically how the COUNTA() function is working.

Thanks for all the help. I'll start on the hours and post if I need any help with that.
Also, still waiting on an explanation of why not to use merged cells.

Kevin

**Palmetto** · 12-31-2009, 09:41 AM

I do have a question, though. I like to know how the code is working, so I was wondering what was going on with the following lines of code:

.Range("A3:B" & lrow).AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Sheet2.Range("T1:T2"), CopyToRange:=Sheet2.Range("T3"), Unique:=False

That line of code applies Advanced Filter to the range of cells, specifies the criteria cells for the filter and directs where to put the filtered results.

also . . . specifically how the COUNTA() function is working.

.Names.Add Name:="Spearpoint", RefersToR1C1:="=OFFSET(inventory!R4C9,0,0,COUNTA(inventory!C9),1)"

See the Excel Help for an explanation of COUNTA. The code is using the R1C1 notation (not A1 style). C9 refers to column-9 ("I") and is counting the number of non-blank rows in the column.

Also, still waiting on an explanation of why not to use merged cells.

They are problematic for VBA coding. It is best to avoid their use altogether, IMO. Use "Center Across Selection" instead.

Note: If you are satisfied with your solution, please mark this thread as solved by going to the top of YOUR first post and clicking on Thread Tools, then Go Advanced. Don't forget to leave feed back and add to reputations - see my signature.