Using value from ComboBox in AdvancedFilter VBA code

[Skippy]

I'm struggling with the advanced filter using VBA.

In my case, the user selects an item from a ComboBox (e.g. "Apple"). Then, from a worksheet, I want to copy all unique "Brands" from Col G that match "Apple" (col B) to the last column in the sheet.

This is what I'm trying, which doesn't work. The problem I believe is with setting the CriteriaRange. Can anyone help out?

With wsSheet 
     ' Add the filter criteria
     ' column heading (e.g. Fruit)
    .Range("IU1").Value = .Range("B1") 
     ' item selected from a combobox (e.g. Apple)
    .Range("IU2").Value = frmOptions.cboFrom.Value 
     
     ' Define the range for the Advanced Filter (e.g. Brand).
    Set rngData = .Range(.Range("G1"), .Range("G65536").End(xlUp)) 
     
     ' Create a unique list and copy to Last column.
    rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _ 
    .Range("IU1:IU2"), CopyToRange:=Range("IV1"), Unique:=True 
     
     ' Read the filtered unique list into a variant array.
    varData = .Range(.Range("IV2"), .Range("IV65536").End(xlUp)).Value 
     
     ' Delete the temporarily created unique list.
    .Range(.Range("IV1"), .Range("IV65536").End(xlUp)).ClearContents 
    .Range("IU1:IU2").ClearContents 
End With

 

[Skippy]

I should add that this message has also been posted at http://www.ozgrid.com/forum/showthread.php?postid=217192#poststop

 

[jindon]

Hi

Are the Range("B1") and Range("G1") same?

They should be labels.

Otherwise I can not see any problem.

 

[Tazguy37]

I'm struggling with the advanced filter using VBA.

In my case, the user selects an item from a ComboBox (e.g. "Apple"). Then, from a worksheet, I want to copy all unique "Brands" from Col G that match "Apple" (col B) to the last column in the sheet.

This is what I'm trying, which doesn't work. The problem I believe is with setting the CriteriaRange. Can anyone help out?

Are they control toolbox comboboxes, or forms comboboxes?

If they're from the control toolbox, what you're doing would probably work, if the Linked Cell property of each box is linked to a cell in Column B.
If they're forms comboboxes, you'll have to right-click the control, go to Format Control, and select the Cell Link.

Remember that a combobox (or any other ActiveX control) doesn't attach itself to a cell (unless you make it that way, by using its properties or by VBA code). It might look like it's on top if a particular cell, but it's not part of the cell object.

 

[Skippy]

Thanks for the reply jindon. Not sure what you are asking.

Range("B1") = "Fruit"
Range("G1") = "Brand"

What the code does is set:

.Range("IU1").Value = "Fruit"
.Range("IU2").Value = "Apple
 rngData = .Range(.Range("G1"), .Range("G65536").End(xlUp)) 'Retrieves the Brand names for "Brands"

Then I try to apply the AdvancedFilter where CriteriaRange = "Apple" and the Unique range is defined by rngData

 

[Skippy]

Tazguy37
Are they control toolbox comboboxes, or forms comboboxes?

The ComboBox is on a UserForm.

 

[Tazguy37]

What the code does is set:

.Range("IU1").Value = "Fruit"
.Range("IU2").Value = "Apple
 rngData = .Range(.Range("G1"), .Range("G65536").End(xlUp)) 'Retrieves the Brand names for "Brands"

Then I try to apply the AdvancedFilter where CriteriaRange = "Apple" and the Unique range is defined by rngData

I'm not understanding how you're transferring the value from the combobox to B1 (which gets copied to Cell IU1) for use in the filter.

Is there more code that does this?

 

[Skippy]

.Range("IU1").Value = "Fruit"
.Range("IU2").Value = "Apple
 rngData = .Range(.Range("G1"), .Range("G65536").End(xlUp)) 'Retrieves the Brand names for "Brands"

I'm not understanding how you're transferring the value from the combobox to B1 (which gets copied to Cell IU1) for use in the filter.

Is there more code that does this?

These are just examples. The actual code in my original post for these two lines are:

     ' Add the filter criteria 
     ' column heading (e.g. Fruit) 
    .Range("IU1").Value = .Range("B1") 
     ' item selected from a combobox (e.g. Apple) 
    .Range("IU2").Value = frmOptions.cboFrom.Value

. I believe my problem is how I'm setting the CriteriaRange:

     ' Create a unique list and copy to Last column. 
    rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _ 
    .Range("IU1:IU2"), CopyToRange:=Range("IV1"), Unique:=True