| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with my macro for two autofilters
I've written this macro to ask for input and then autofilter two columns
based on that input. The first part works fine but the second part steps on the first autofilter. Would one of you gurus mind taking a look at my code and let me know what's wrong? I'm thinking there must be one simple piece of code that I'm missing. Thanks! Randy Option Compare Text Private Sub Workbook_Open() Dim P As String Dim B As String B = ("") P = InputBox("Please enter the size of your project: S for Small, M for Medium, L for Large or Cancel for All.") 10 If (P) = "l" Or (P) = "m" Or (P) = "s" Then Columns("C:C").Select Selection.AutoFilter Field:=1, Criteria1:=(P) Else: If (P) = "" Then GoTo 99 If (P) < "" Then P = InputBox("Please enter a valid selection: S for Small, M for Medium, L for Large or Cancel for All.") GoTo 10 End If End If If (P) = "l" Or (P) = "m" Then A = MsgBox("Is there a CIR for this project?", _ vbYesNo) If A = vbNo Then Columns("D:D").Select Selection.AutoFilter Field:=1, Criteria1:=(B) Else: GoTo 99 End If End If 99 End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with my macro for two autofilters
Hi
You use (B) and (P) as variables but you have declared B and P, which are different. Replace all your B and P's with (B) and (P)'s (or vice versa would be better). regards Paul On Mar 9, 1:43 pm, Randy wrote: I've written this macro to ask for input and then autofilter two columns based on that input. The first part works fine but the second part steps on the first autofilter. Would one of you gurus mind taking a look at my code and let me know what's wrong? I'm thinking there must be one simple piece of code that I'm missing. Thanks! Randy Option Compare Text Private Sub Workbook_Open() Dim P As String Dim B As String B = ("") P = InputBox("Please enter the size of your project: S for Small, M for Medium, L for Large or Cancel for All.") 10 If (P) = "l" Or (P) = "m" Or (P) = "s" Then Columns("C:C").Select Selection.AutoFilter Field:=1, Criteria1:=(P) Else: If (P) = "" Then GoTo 99 If (P) < "" Then P = InputBox("Please enter a valid selection: S for Small, M for Medium, L for Large or Cancel for All.") GoTo 10 End If End If If (P) = "l" Or (P) = "m" Then A = MsgBox("Is there a CIR for this project?", _ vbYesNo) If A = vbNo Then Columns("D:D").Select Selection.AutoFilter Field:=1, Criteria1:=(B) Else: GoTo 99 End If End If 99 End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with my macro for two autofilters
Maybe this will help. I changed some variables around--dropped the goto's and
line numbers and replaced a couple if's with select case (I find them easier to read when there are multiple choices). And added a loop to get a good choice. And I also changed the filter for the CIR stuff. Either it shows empty cells or filled in cells. That may not be what you want. Option Explicit Option Compare Text Private Sub Workbook_Open() Dim ProjectSize As String Dim resp As Long Dim CIRCrit As String Do ProjectSize = InputBox("Please enter the size of your project:" _ & vbLf & " S for Small" & vbLf & " M for Medium" _ & vbLf & " L for Large" & vbLf & "or Cancel for All.") 'get rid of any leading/trailing spaces ProjectSize = Trim(ProjectSize) Select Case ProjectSize Case Is = "", "s", "m", "l" Exit Do Case Else MsgBox "Invalid choice. Try again" End Select Loop With Worksheets("sheet1") '<--change the name here 'remove any existing filter .AutoFilterMode = False If ProjectSize = "" Then 'all selected, do nothing Else .Range("C:D").AutoFilter field:=1, Criteria1:=ProjectSize End If Select Case ProjectSize Case Is = "l", "m" resp = MsgBox("Is there resp CIR for this project?", vbYesNo) If resp = vbNo Then CIRCrit = "" Else CIRCrit = "<" End If .Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit End Select End With End Sub Randy wrote: I've written this macro to ask for input and then autofilter two columns based on that input. The first part works fine but the second part steps on the first autofilter. Would one of you gurus mind taking a look at my code and let me know what's wrong? I'm thinking there must be one simple piece of code that I'm missing. Thanks! Randy Option Compare Text Private Sub Workbook_Open() Dim P As String Dim B As String B = ("") P = InputBox("Please enter the size of your project: S for Small, M for Medium, L for Large or Cancel for All.") 10 If (P) = "l" Or (P) = "m" Or (P) = "s" Then Columns("C:C").Select Selection.AutoFilter Field:=1, Criteria1:=(P) Else: If (P) = "" Then GoTo 99 If (P) < "" Then P = InputBox("Please enter a valid selection: S for Small, M for Medium, L for Large or Cancel for All.") GoTo 10 End If End If If (P) = "l" Or (P) = "m" Then A = MsgBox("Is there a CIR for this project?", _ vbYesNo) If A = vbNo Then Columns("D:D").Select Selection.AutoFilter Field:=1, Criteria1:=(B) Else: GoTo 99 End If End If 99 End Sub -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with my macro for two autofilters
Dave,
I need the CIR filter to either show all cells if yes or only blanks if no. Thanks for the help! Randy "Dave Peterson" wrote: Maybe this will help. I changed some variables around--dropped the goto's and line numbers and replaced a couple if's with select case (I find them easier to read when there are multiple choices). And added a loop to get a good choice. And I also changed the filter for the CIR stuff. Either it shows empty cells or filled in cells. That may not be what you want. Option Explicit Option Compare Text Private Sub Workbook_Open() Dim ProjectSize As String Dim resp As Long Dim CIRCrit As String Do ProjectSize = InputBox("Please enter the size of your project:" _ & vbLf & " S for Small" & vbLf & " M for Medium" _ & vbLf & " L for Large" & vbLf & "or Cancel for All.") 'get rid of any leading/trailing spaces ProjectSize = Trim(ProjectSize) Select Case ProjectSize Case Is = "", "s", "m", "l" Exit Do Case Else MsgBox "Invalid choice. Try again" End Select Loop With Worksheets("sheet1") '<--change the name here 'remove any existing filter .AutoFilterMode = False If ProjectSize = "" Then 'all selected, do nothing Else .Range("C:D").AutoFilter field:=1, Criteria1:=ProjectSize End If Select Case ProjectSize Case Is = "l", "m" resp = MsgBox("Is there resp CIR for this project?", vbYesNo) If resp = vbNo Then CIRCrit = "" Else CIRCrit = "<" End If .Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit End Select End With End Sub Randy wrote: I've written this macro to ask for input and then autofilter two columns based on that input. The first part works fine but the second part steps on the first autofilter. Would one of you gurus mind taking a look at my code and let me know what's wrong? I'm thinking there must be one simple piece of code that I'm missing. Thanks! Randy Option Compare Text Private Sub Workbook_Open() Dim P As String Dim B As String B = ("") P = InputBox("Please enter the size of your project: S for Small, M for Medium, L for Large or Cancel for All.") 10 If (P) = "l" Or (P) = "m" Or (P) = "s" Then Columns("C:C").Select Selection.AutoFilter Field:=1, Criteria1:=(P) Else: If (P) = "" Then GoTo 99 If (P) < "" Then P = InputBox("Please enter a valid selection: S for Small, M for Medium, L for Large or Cancel for All.") GoTo 10 End If End If If (P) = "l" Or (P) = "m" Then A = MsgBox("Is there a CIR for this project?", _ vbYesNo) If A = vbNo Then Columns("D:D").Select Selection.AutoFilter Field:=1, Criteria1:=(B) Else: GoTo 99 End If End If 99 End Sub -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with my macro for two autofilters
Option Compare Text
Private Sub Workbook_Open() Dim P As String Dim B As String B = ("") P = InputBox("Please enter the size of your project: S for Small, M for Medium, L for Large or Cancel for All.") 10 If (P) = "l" Or (P) = "m" Or (P) = "s" Then Columns("C:D").Select Selection.AutoFilter Field:=1, Criteria1:=(P) Else: If (P) = "" Then GoTo 99 If (P) < "" Then P = InputBox("Please enter a valid selection: S for Small, M for Medium, L for Large or Cancel for All.") GoTo 10 End If End If If (P) = "l" Or (P) = "m" Then A = MsgBox("Is there a CIR for this project?", _ vbYesNo) If A = vbNo Then Columns("C:D").Select Selection.AutoFilter Field:=2, Criteria1:=(B) Else: GoTo 99 End If End If 99 End Sub -- Regards, Tom Ogilvy "Randy" wrote: I've written this macro to ask for input and then autofilter two columns based on that input. The first part works fine but the second part steps on the first autofilter. Would one of you gurus mind taking a look at my code and let me know what's wrong? I'm thinking there must be one simple piece of code that I'm missing. Thanks! Randy Option Compare Text Private Sub Workbook_Open() Dim P As String Dim B As String B = ("") P = InputBox("Please enter the size of your project: S for Small, M for Medium, L for Large or Cancel for All.") 10 If (P) = "l" Or (P) = "m" Or (P) = "s" Then Columns("C:C").Select Selection.AutoFilter Field:=1, Criteria1:=(P) Else: If (P) = "" Then GoTo 99 If (P) < "" Then P = InputBox("Please enter a valid selection: S for Small, M for Medium, L for Large or Cancel for All.") GoTo 10 End If End If If (P) = "l" Or (P) = "m" Then A = MsgBox("Is there a CIR for this project?", _ vbYesNo) If A = vbNo Then Columns("D:D").Select Selection.AutoFilter Field:=1, Criteria1:=(B) Else: GoTo 99 End If End If 99 End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with my macro for two autofilters
b = 100
? b 100 ? (b) 100 Now you can't do (b) = 20 -- Regards, Tom Ogilvy " wrote: Hi You use (B) and (P) as variables but you have declared B and P, which are different. Replace all your B and P's with (B) and (P)'s (or vice versa would be better). regards Paul On Mar 9, 1:43 pm, Randy wrote: I've written this macro to ask for input and then autofilter two columns based on that input. The first part works fine but the second part steps on the first autofilter. Would one of you gurus mind taking a look at my code and let me know what's wrong? I'm thinking there must be one simple piece of code that I'm missing. Thanks! Randy Option Compare Text Private Sub Workbook_Open() Dim P As String Dim B As String B = ("") P = InputBox("Please enter the size of your project: S for Small, M for Medium, L for Large or Cancel for All.") 10 If (P) = "l" Or (P) = "m" Or (P) = "s" Then Columns("C:C").Select Selection.AutoFilter Field:=1, Criteria1:=(P) Else: If (P) = "" Then GoTo 99 If (P) < "" Then P = InputBox("Please enter a valid selection: S for Small, M for Medium, L for Large or Cancel for All.") GoTo 10 End If End If If (P) = "l" Or (P) = "m" Then A = MsgBox("Is there a CIR for this project?", _ vbYesNo) If A = vbNo Then Columns("D:D").Select Selection.AutoFilter Field:=1, Criteria1:=(B) Else: GoTo 99 End If End If 99 End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with my macro for two autofilters
If resp = vbNo Then
CIRCrit = "" Else CIRCrit = "<" End If .Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit becomes: If resp = vbNo Then CIRCrit = "" .Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit end if Randy wrote: Dave, I need the CIR filter to either show all cells if yes or only blanks if no. Thanks for the help! Randy "Dave Peterson" wrote: Maybe this will help. I changed some variables around--dropped the goto's and line numbers and replaced a couple if's with select case (I find them easier to read when there are multiple choices). And added a loop to get a good choice. And I also changed the filter for the CIR stuff. Either it shows empty cells or filled in cells. That may not be what you want. Option Explicit Option Compare Text Private Sub Workbook_Open() Dim ProjectSize As String Dim resp As Long Dim CIRCrit As String Do ProjectSize = InputBox("Please enter the size of your project:" _ & vbLf & " S for Small" & vbLf & " M for Medium" _ & vbLf & " L for Large" & vbLf & "or Cancel for All.") 'get rid of any leading/trailing spaces ProjectSize = Trim(ProjectSize) Select Case ProjectSize Case Is = "", "s", "m", "l" Exit Do Case Else MsgBox "Invalid choice. Try again" End Select Loop With Worksheets("sheet1") '<--change the name here 'remove any existing filter .AutoFilterMode = False If ProjectSize = "" Then 'all selected, do nothing Else .Range("C:D").AutoFilter field:=1, Criteria1:=ProjectSize End If Select Case ProjectSize Case Is = "l", "m" resp = MsgBox("Is there resp CIR for this project?", vbYesNo) If resp = vbNo Then CIRCrit = "" Else CIRCrit = "<" End If .Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit End Select End With End Sub Randy wrote: I've written this macro to ask for input and then autofilter two columns based on that input. The first part works fine but the second part steps on the first autofilter. Would one of you gurus mind taking a look at my code and let me know what's wrong? I'm thinking there must be one simple piece of code that I'm missing. Thanks! Randy Option Compare Text Private Sub Workbook_Open() Dim P As String Dim B As String B = ("") P = InputBox("Please enter the size of your project: S for Small, M for Medium, L for Large or Cancel for All.") 10 If (P) = "l" Or (P) = "m" Or (P) = "s" Then Columns("C:C").Select Selection.AutoFilter Field:=1, Criteria1:=(P) Else: If (P) = "" Then GoTo 99 If (P) < "" Then P = InputBox("Please enter a valid selection: S for Small, M for Medium, L for Large or Cancel for All.") GoTo 10 End If End If If (P) = "l" Or (P) = "m" Then A = MsgBox("Is there a CIR for this project?", _ vbYesNo) If A = vbNo Then Columns("D:D").Select Selection.AutoFilter Field:=1, Criteria1:=(B) Else: GoTo 99 End If End If 99 End Sub -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with my macro for two autofilters
Actually, I changed "<" to "<<" and it seems to be working fine. I wonder
why I'm not getting notified of replies even though I check the box? Thanks again Dave! "Dave Peterson" wrote: If resp = vbNo Then CIRCrit = "" Else CIRCrit = "<" End If .Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit becomes: If resp = vbNo Then CIRCrit = "" .Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit end if Randy wrote: Dave, I need the CIR filter to either show all cells if yes or only blanks if no. Thanks for the help! Randy "Dave Peterson" wrote: Maybe this will help. I changed some variables around--dropped the goto's and line numbers and replaced a couple if's with select case (I find them easier to read when there are multiple choices). And added a loop to get a good choice. And I also changed the filter for the CIR stuff. Either it shows empty cells or filled in cells. That may not be what you want. Option Explicit Option Compare Text Private Sub Workbook_Open() Dim ProjectSize As String Dim resp As Long Dim CIRCrit As String Do ProjectSize = InputBox("Please enter the size of your project:" _ & vbLf & " S for Small" & vbLf & " M for Medium" _ & vbLf & " L for Large" & vbLf & "or Cancel for All.") 'get rid of any leading/trailing spaces ProjectSize = Trim(ProjectSize) Select Case ProjectSize Case Is = "", "s", "m", "l" Exit Do Case Else MsgBox "Invalid choice. Try again" End Select Loop With Worksheets("sheet1") '<--change the name here 'remove any existing filter .AutoFilterMode = False If ProjectSize = "" Then 'all selected, do nothing Else .Range("C:D").AutoFilter field:=1, Criteria1:=ProjectSize End If Select Case ProjectSize Case Is = "l", "m" resp = MsgBox("Is there resp CIR for this project?", vbYesNo) If resp = vbNo Then CIRCrit = "" Else CIRCrit = "<" End If .Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit End Select End With End Sub Randy wrote: I've written this macro to ask for input and then autofilter two columns based on that input. The first part works fine but the second part steps on the first autofilter. Would one of you gurus mind taking a look at my code and let me know what's wrong? I'm thinking there must be one simple piece of code that I'm missing. Thanks! Randy Option Compare Text Private Sub Workbook_Open() Dim P As String Dim B As String B = ("") P = InputBox("Please enter the size of your project: S for Small, M for Medium, L for Large or Cancel for All.") 10 If (P) = "l" Or (P) = "m" Or (P) = "s" Then Columns("C:C").Select Selection.AutoFilter Field:=1, Criteria1:=(P) Else: If (P) = "" Then GoTo 99 If (P) < "" Then P = InputBox("Please enter a valid selection: S for Small, M for Medium, L for Large or Cancel for All.") GoTo 10 End If End If If (P) = "l" Or (P) = "m" Then A = MsgBox("Is there a CIR for this project?", _ vbYesNo) If A = vbNo Then Columns("D:D").Select Selection.AutoFilter Field:=1, Criteria1:=(B) Else: GoTo 99 End If End If 99 End Sub -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with my macro for two autofilters
Glad you got it working.
And I don't use the MS web based interface--so I don't have a guess about the checkbox stuff. Randy wrote: Actually, I changed "<" to "<<" and it seems to be working fine. I wonder why I'm not getting notified of replies even though I check the box? Thanks again Dave! "Dave Peterson" wrote: If resp = vbNo Then CIRCrit = "" Else CIRCrit = "<" End If .Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit becomes: If resp = vbNo Then CIRCrit = "" .Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit end if Randy wrote: Dave, I need the CIR filter to either show all cells if yes or only blanks if no. Thanks for the help! Randy "Dave Peterson" wrote: Maybe this will help. I changed some variables around--dropped the goto's and line numbers and replaced a couple if's with select case (I find them easier to read when there are multiple choices). And added a loop to get a good choice. And I also changed the filter for the CIR stuff. Either it shows empty cells or filled in cells. That may not be what you want. Option Explicit Option Compare Text Private Sub Workbook_Open() Dim ProjectSize As String Dim resp As Long Dim CIRCrit As String Do ProjectSize = InputBox("Please enter the size of your project:" _ & vbLf & " S for Small" & vbLf & " M for Medium" _ & vbLf & " L for Large" & vbLf & "or Cancel for All.") 'get rid of any leading/trailing spaces ProjectSize = Trim(ProjectSize) Select Case ProjectSize Case Is = "", "s", "m", "l" Exit Do Case Else MsgBox "Invalid choice. Try again" End Select Loop With Worksheets("sheet1") '<--change the name here 'remove any existing filter .AutoFilterMode = False If ProjectSize = "" Then 'all selected, do nothing Else .Range("C:D").AutoFilter field:=1, Criteria1:=ProjectSize End If Select Case ProjectSize Case Is = "l", "m" resp = MsgBox("Is there resp CIR for this project?", vbYesNo) If resp = vbNo Then CIRCrit = "" Else CIRCrit = "<" End If .Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit End Select End With End Sub Randy wrote: I've written this macro to ask for input and then autofilter two columns based on that input. The first part works fine but the second part steps on the first autofilter. Would one of you gurus mind taking a look at my code and let me know what's wrong? I'm thinking there must be one simple piece of code that I'm missing. Thanks! Randy Option Compare Text Private Sub Workbook_Open() Dim P As String Dim B As String B = ("") P = InputBox("Please enter the size of your project: S for Small, M for Medium, L for Large or Cancel for All.") 10 If (P) = "l" Or (P) = "m" Or (P) = "s" Then Columns("C:C").Select Selection.AutoFilter Field:=1, Criteria1:=(P) Else: If (P) = "" Then GoTo 99 If (P) < "" Then P = InputBox("Please enter a valid selection: S for Small, M for Medium, L for Large or Cancel for All.") GoTo 10 End If End If If (P) = "l" Or (P) = "m" Then A = MsgBox("Is there a CIR for this project?", _ vbYesNo) If A = vbNo Then Columns("D:D").Select Selection.AutoFilter Field:=1, Criteria1:=(B) Else: GoTo 99 End If End If 99 End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro that AutoFilters based on Cell value | Excel Programming | |||
| AutoFilters | Excel Worksheet Functions | |||
| How to set up AutoFilters? | Excel Worksheet Functions | |||
| VBA and Autofilters | Excel Programming | |||
| Userforms and autofilters - Autofilters don't seen to work with userform | Excel Programming | |||
Linear Mode
