RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 810
- Office Version
- 365
- Platform
- 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:
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.
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
) would be YES if it contained the 4 categories matching the 4 checkboxes
