I'm picking multiple options from a Userform and need the macro to look in another sheet and return results based on those options.

R

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
790
Office Version
  1. 365
Platform
  1. Windows
Good morning everyone.

I have a userform that shows multiple options, for example 12 checkboxes for the month, 4 checkboxes for categories and some radio buttons here and there.

I need to be able to choose one or more months, one or more options and then press go and have it look into another sheet (PP) and start looping down, copying the contents of certain cells over to the main sheet (TW) for anything applicable to the options.

As an example, if I picked FEB and MAR for "GB" and "EU" it would loop down and pick all products that match a FEB or MAR date which fall into either a "GB" or "EU" category.

Here's what I have below:

  • I set declarations
  • If something is checked, it is written into a new sheet called PPTemp
  • PPTemp has 2 columns, one showing all chosen months and another showing all chosen products (A & C)

At this point, I'm stuck. The critical part is I don't know how to say in VBA terms, if the contents of Column N in PP match the Months declared, AND if the category matches, then start returning results.

Like how do I say "If cell = list"?

Thanks.


Code: 
Set TW = Worksheets("Tour Weighting 1")


Dim Lastrow As Long, LastrowCat As Long, PPTemp As Worksheet


Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PPTemp").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add.Name = "PPTemp"
Set PPTemp = Worksheets("PPTemp")
PPTemp.Move before:=TW


PPTemp.Activate
Range("A1").Value = "Month"
Range("B1").Value = "Cat"
Range("C1").Value = "Category"




TW.Activate




If CBJAN = True Then
Jan = "OK"
PPTemp.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Lastrow).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Jan"
TW.Activate
Else
Jan = "No"
End If


If CBFEB = True Then
Feb = "OK"
PPTemp.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Lastrow).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Feb"
TW.Activate
Else
Feb = "No"
End If


If CBMAR = True Then
Mar = "OK"
PPTemp.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Lastrow).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Mar"
TW.Activate
Else
Mar = "No"
End If


If CBAPR = True Then
Apr = "OK"
PPTemp.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Lastrow).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Apr"
TW.Activate
Else
Apr = "No"
End If


If CBMAY = True Then
May = "OK"
PPTemp.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Lastrow).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "May"
TW.Activate
Else
May = "No"
End If


If CBJUN = True Then
Jun = "OK"
PPTemp.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Lastrow).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Jun"
TW.Activate
Else
Jun = "No"
End If


If CBJUL = True Then
Jul = "OK"
PPTemp.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Lastrow).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Jul"
TW.Activate
Else
Jul = "No"
End If


If CBAUG = True Then
Aug = "OK"
PPTemp.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Lastrow).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Aug"
TW.Activate
Else
Aug = "No"
End If


If CBSEP = True Then
PPTemp.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Lastrow).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Sep"
TW.Activate
Sep = "OK"
Else
Sep = "No"
End If


If CBOCT = True Then
October = "OK"
PPTemp.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Lastrow).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Oct"
TW.Activate
Else
October = "No"
End If


If CBNOV = True Then
Nov = "OK"
PPTemp.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Lastrow).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Nov"
TW.Activate
Else
Nov = "No"
End If


If CBDEC = True Then
Dec = "OK"
PPTemp.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Lastrow).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Dec"
TW.Activate
Else
Dec = "No"
End If


If CBJGGB1 = True Then
JGGB = "OK"
PPTemp.Activate
Range("C2").Activate
LastrowCat = Cells(Rows.Count, "C").End(xlUp).Row
Range("C" & LastrowCat).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "JG GB"
TW.Activate
Else
JGGB = "No"
End If


If CBJGEU1 = True Then
JGEU = "OK"
PPTemp.Activate
LastrowCat = Cells(Rows.Count, "C").End(xlUp).Row
Range("C" & LastrowCat).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "JG EU"
TW.Activate
Else
JGEU = "No"
End If


If CBJGE1 = True Then
JGE = "OK"
PPTemp.Activate
LastrowCat = Cells(Rows.Count, "C").End(xlUp).Row
Range("C" & LastrowCat).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "JG Events"
TW.Activate
Else
JGE = "No"
End If


If CBJGSV1 = True Then
JGSV = "OK"
PPTemp.Activate
LastrowCat = Cells(Rows.Count, "C").End(xlUp).Row
Range("C" & LastrowCat).Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Supervalue"
TW.Activate
Else
JGSV = "No"
End If


PPTemp.Activate




Set PP = Workbooks.Open("H:\Sales\Price Panels\Price Panels 2019.xlsm", ReadOnly:=True)




PP.Activate


Range("A3").Activate


Do Until Cells(ActiveCell.Row, "A").Value = ""
    If Cells(ActiveCell.Row, "B").Value <> "Active" Then
    ActiveCell.Offset(1, 0).Activate
    Else
    
    
    
    End If


    ActiveCell.Offset(1, 0).Activate
    Loop


End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
1. The fastest way to get the data is to filter it and then copy the filtered rows
2. To make coding as simple as possible use logical names for the objects on your userform that can be used DIRECTLY to filter the data
(The names used depends on what your data looks like)

An example to illustrate
- data category values in cells are EU GB USA Other

Obvious names:
12 checkboxes for months - names are: cb_01 to cb_12
4 checkboxes for categories - names are: cb_EU cb_GB cb_USA cb_Other

Why those names?
- VBA can test every checkbox to see if checked
- if checked, remove cb_ from the name and the remaining string becomes the filter value

Questions
Q1 Does your data contain dates or month numbers ?
Q2 If your data contains dates, is January month 01 ?
Q3 Are all category values in the same single column?
Q4 "userform has some radio buttons here and there"
- what is the relevance of the radio buttons ?
- how do they affect what is filtered ?
 
Upvote 0
Thanks for getting back to me. In my code they're listed as "CBJAN" etc.

1 - The Data in PP has dates AND month numbers. In Column N it is specifically the three-letter shorthand for each Month, eg "Jan", "Feb", "Mar" etc.

2 - N/A

3 - All category values are in the same column, but they are translated using a table. So column B in PPTemp will have values such as "1", "2", "E", "L" etc

For instance, GB would be 1 and 2, Events is E and F, and so on like this.

In PP, these code values are in Column Y.


I need to work on a way so that when I select "GB" it places 1 and 2 down, when I select SV it places 7, 8 and 9 down. This is simple enough to do but good practice to make life easier.

4 - These shouldn't be of any concern now, it's just buttons to erase the data and start again or append to existing. I don't think I'll need help with these so for now they are irrelevant. Thanks
 
Upvote 0
This is what I have now:

Code: 
' Set newly found variables

Dim MonthRNG As Range, CatRNG As Range


Set MonthRNG = Range("A2:A13")
Set CatRNG = Range("B2:B10")




Set PP = Workbooks.Open("H:\Sales\Price Panels\Price Panels 2019.xlsm", ReadOnly:=True)


PP.Activate
Range("A3").Activate


Dim PPLastrow As Long
PPLastrow = Cells(Rows.Count, "A").End(xlUp).Row


ActiveSheet.Range("A2:AF" & PPLastrow).AutoFilter field:=2, Criteria:="Active"
ActiveSheet.Range("A2:AF" & PPLastrow).AutoFilter field:=14, Criteria:=MonthRNG
ActiveSheet.Range("A2:AF" & PPLastrow).AutoFilter field:=25, Criteria:=CatRNG
ActiveSheet.Range("A2:AF" & PPLastrow).AutoFilter field:=31, Criteria:=">18"

On the first Activesheet.range, I am getting "Application defined or object defined error"

I am trying to filter down the hefty PP file by the following criteria:

Field 2 (Column B I assume?) must equal Active

Field 14 must contain a cell named in MonthRNG range (which is May + Jun)

Field 25 must contain a cell named in CatRNG (which is 7, 8, 9, E or F)

Field 31 must have a number greater than or 18.


When I do this manually, I am left with 64 results which I'm certain I can easily loop down and copy over.

However I can't seem to get the filters to apply even a basic instruction, much less instruction from a list.
 
Upvote 0
1 - The Data in PP has dates AND month numbers. In Column N it is specifically the three-letter shorthand for each Month, eg "Jan", "Feb", "Mar" etc.
Click to expand...
Are values in column N ...
- dates but formatted "mmm" to give Jan, Feb etc
- a formula (where column D holds the actual date) =TEXT(D2,"mmm")
- text values ie literally Jan, Feb etc
(it matters for filtering)


All category values are in the same column, but they are translated using a table. So column B in PPTemp will have values such as "1", "2", "E", "L" etc
For instance, GB would be 1 and 2, Events is E and F, and so on like this.
In PP, these code values are in Column Y
Click to expand...

Are you saying that column Y contains the results from looking up the table?
That would allow VBA to filter directly on the value in column Y
( ie filter on GB rather than having to look up the table to find out which values (1 & 2) to use )
 
Upvote 0
Yongle said:
Are values in column N ...
- dates but formatted "mmm" to give Jan, Feb etc
- a formula (where column D holds the actual date) =TEXT(D2,"mmm")
- text values ie literally Jan, Feb etc
(it matters for filtering)




Are you saying that column Y contains the results from looking up the table?
That would allow VBA to filter directly on the value in column Y
( ie filter on GB rather than having to look up the table to find out which values (1 & 2) to use )
Click to expand...


Hi Yongle,

Thankfully it's the simple solution to both questions.

N is values, literally Jan, Feb.

Y are also values, literally 1, 2, 3, E, F, L, etc.


In PPTemp I now have just 2 columns. One has the applicable month, let's use May and Jun and the other has the applicable category, let's use 7, 8, 9, E and F.


So the issue now comes to applying these variables to the filter. Where I am getting application or object defined error. Thanks.
 
Upvote 0
We are not communicating well here which is rather frustrating
Let's reset

For clarification
- My unserstanding is that there are 4 category checkboxes
- so far I have 3 categories which are the EU, UK (from post#1) & Events(mentioned in post#3) - what is the 4th category?

Explanation
What I asked is "Are you saying that column Y contains the results from looking up the table?"
- the answer is NO because it contains 1, 2, 3, E, F, L
- the answer (from my way of looking at things :) ) would be YES if it contained the 4 categories matching the 4 checkboxes

I want to create a rule for VBA to be able to
- when checkboxGB is checked to look up GB in the table so that columnY is filtered on only 1 and 2 (which is what you said in post#3)
- when checkboxlEvents is checked to lookup Events in the table so that columnY is filtered only on E & F (which is what you said in post#3)

Column Y contains more than 4 different values but there are only 4 category checkboxes and VBA needs to be told somehow that 1= GB and 2 = GB , E = Events, F = Events etc
- which sheet in which workbook contains the lookup table ?
- which column on that sheet contains "the 4 checkbox categories" ?
- and which column on that sheet contains 1 , 2, 3, E, F, L etc ?
 
Upvote 0
Hi Yongle,

I fear we are indeed miscommunicating.

I don't think those details matter. Let's break it down.

When the user chooses any number of the 12 dates, these are put in PPTemp Column A, descending as "Jan" "Feb "Mar" etc.

When the user chooses any of the four categories, the translated categories are placed in Column B, descending, as "1" "2" "3" "6" "E" "F" "7" "8" "9"


A2:A13 is declared as "MonthRNG"
B2:B10 is declared as "CatRNG"


As an example, I will choose the months of May and Jun and the categories Events and SV

This results in the following being output to PPTemp

AB
1MonthCategory
2MayE
3JunF
47
58
69
7

<tbody>
</tbody>


I then switch to PP and I need a macro that filters to the following criteria:

Column B must contain "Active"

Code: 
[B][COLOR=#006400]Range("A2:AF" & PPLastrow).AutoFilter Field:=2, Criteria1:="Active"[/COLOR][/B]

Column N must contain any and all Month names found in MonthRNG

Code: 
[COLOR=#b22222][B]Range("A2:AF" & PPLastrow).AutoFilter Field:=14, Criteria:=MonthRNG[/B][/COLOR]

Column Y must contain any and all Category names found in CatRNG

Code: 
[COLOR=#a52a2a][B]Range("A2:AF" & PPLastrow).AutoFilter Field:=25, Criteria:=CatRNG[/B][/COLOR][CODE]
[/CODE]

And finally, column AE must contain a number greater than 18.

Code: 
[COLOR=#a52a2a][B]Range("A2:AF" & PPLastrow).AutoFilter Field:=31, Criteria1:=">18"[/B][/COLOR]


The first one works but the last three produce errors which I've detailed above.

Hoping this helps clear things up, the details don't matter, I have declared two columns as ranges and I need the filter to pick up on what's in the range and apply it to the column specified.

NB - I've been messing aroung with Criteria, Criteria1 and Criteria2 to see if anything works and it doesn't.
 
Upvote 0
Have a look at this - hopefuly self explanatory

BEFORE
Excel 2016 (Windows) 32 bit
A​
B​
C​
D​
E​
F​
1​
Months
Live
Category
Value
2​
MayactiveE
15​
3​
JunactiveF
19​
xx
4​
JulactiveE
19​
5​
AugF
19​
6​
SepactiveE
19​
7​
MayF
19​
8​
JunactiveE
19​
xx
9​
JulF
19​
10​
AugactiveE
19​
11​
SepF
19​
12​
MayactiveE
19​
xx
13​
JunF
19​
14​
JulactiveE
19​
15​
AugF
19​
16​
SepactiveE
19​
17​
Maya
19​
18​
Junactiveb
19​
19​
Julc
19​
20​
Augactived
19​
21​
Sepe
19​
22​
Mayactive
7​
19​
xx
23​
Junactive
8​
19​
xx
24​
Julactive
9​
19​
25​
Aug
7​
19​
26​
Sepactive
8​
19​
27​
May
9​
19​
28​
Junactive
9​
19​
xx
29​
Jul
8​
19​
30​
Augactive
9​
19​
31​
Sep
8​
19​
32​
Mayactive
9​
10​
33​
Sheet: Data

AFTER

Excel 2016 (Windows) 32 bit
A​
B​
C​
D​
E​
F​
1​
Months
Live
Category
Value
3​
JunactiveF
19​
xx
8​
JunactiveE
19​
xx
12​
MayactiveE
19​
xx
22​
Mayactive
7​
19​
xx
23​
Junactive
8​
19​
xx
28​
Junactive
9​
19​
xx
33​
Sheet: Data

CRITERIA

Excel 2016 (Windows) 32 bit
A​
B​
C​
1​
MonthCategory
2​
MayE
3​
JunF
4​
7​
5​
8​
6​
9​
7​
Sheet: Criteria

CODE

Code: 
Sub FilterRangeCriteria()
    Dim v_1 As Variant, v_2 As Variant, a As Long
    Dim wsData As Worksheet, wsCrit As Worksheet, rngData As Range
    Set wsData = Worksheets("Data")
    Set wsCrit = Worksheets("Criteria")
    Set rngData = wsData.Range("A:Z")
'array of values for Months and Categories
    v_1 = wsCrit.Range("A2", wsCrit.Range("A" & Rows.Count).End(xlUp)).Value
    v_2 = wsCrit.Range("B2", wsCrit.Range("B" & Rows.Count).End(xlUp)).Value
'convert number categories to text (otherwise filter does not recognise)
    For a = 1 To UBound(v_2)
        v_2(a, 1) = CStr(v_2(a, 1))
    Next a
'filter
    rngData.AutoFilter Field:=1, Criteria1:=Application.Transpose(v_1), Operator:=xlFilterValues    '  months
    rngData.AutoFilter Field:=3, Criteria1:=Application.Transpose(v_2), Operator:=xlFilterValues    '  categories
    rngData.AutoFilter Field:=4, Criteria1:=">18", Operator:=xlFilterValues                         '  >18
    rngData.AutoFilter Field:=2, Criteria1:="active", Operator:=xlFilterValues                      '  active
End Sub
 
Upvote 0
On previous post XX placed manually beforehand simply to verify values filtered by macro
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
ActiveCell vs Active range search
Replies
3
Views
227
Joe4
Joe4
N
  • Question
My code is suddenly really slow
Replies
1
Views
137
RobP
R
C
  • Question
A Bookmark to return to the same row when exiting and opening the application
Replies
8
Views
172
Edgar_
Edgar_
C
  • Question
Copy and paste data through multiple workbooks
Replies
2
Views
203
CDaviess
C
B
  • Question
Copy Paste from one Workbook to another
Replies
1
Views
217
rollis13
rollis13

Forum statistics

Threads
1,215,054
Messages
6,122,895
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top