Hi all. I have a workbook that charts data for survey results. The workbook currently works great. However, I'm now using four user form combo boxes and a macro button to allow uesrs to filter results based on Survey # (we give out the survey somewhat frequently, so we assign each group of surveys a number,) Time With company, etc. Again, there are four filters. I'm using this code to search through our sheet containing all the data, and populate a new sheet with the filtered data:
As you can see, this is only for when the user wants to show all of tha data in each drop down. The problem with coding it this way is that since there are four drop downs for the user to choose from and each one has a choice to show all data, it would require 16 if and elseif statements for it to work. In other words, the user could select a specific item from one drop down, but leave the other three on all data, or he could select specific items from two drop downs but leave the other three on all data, etc.
Is there some way for me to tell vba to ignore a part of my code if the all data option is selected? Here is an example of what I want to do, but need more vba knowledge to accomplish:
I know, this probably isn't possible, but I'd rather not have to use a gazillion if statements. Also, if you know of a better way to do this, or think that this isn't possible, please let me know so I can start thinking of some other way to accomplish my goal. Any help is greatly appreciated!Thanks a lot for the looks.
Hank
Code:
If Period1 = "All Periods" And Period2 = "All Periods" And DurObs = "All Durations of Observation" And DurPos = "All Durations in Position" Then
DataRangeNum = Sheets("Data_Import_Infopath").Range("AE" & Rows.Count).End(xlUp).Row
Sheets("Filtered_Data").Range("A4:A" & (DataRangeNum + 3)).Value = Sheets("Data_Import_Infopath").Range("AE1:AE" & DataRangeNum).Value
End IfAs you can see, this is only for when the user wants to show all of tha data in each drop down. The problem with coding it this way is that since there are four drop downs for the user to choose from and each one has a choice to show all data, it would require 16 if and elseif statements for it to work. In other words, the user could select a specific item from one drop down, but leave the other three on all data, or he could select specific items from two drop downs but leave the other three on all data, etc.
Is there some way for me to tell vba to ignore a part of my code if the all data option is selected? Here is an example of what I want to do, but need more vba knowledge to accomplish:
Code:
Sub GetItDone()
Dim RowNum As Long
For RowNum = 2 to Sheets("Data_Import_Infopath").Range("AE" & Rows.Count).End(xlUp).Row
If Sheets("Data_Import_Infopath").Range("AF" & RowNum).Value = DurationPositionDropDown (code here to tell it to ignore if DurationPositionDropdown = All Durations in Position) And Sheets("Data_Import_Infopath").Range("AG" & RowNum).Value = DurationObservationDropDown (code here to tell it to ignore if DurationObservationDropdown = All Durations of Observation) etc. Then
Do This
Exit For
End SubI know, this probably isn't possible, but I'd rather not have to use a gazillion if statements. Also, if you know of a better way to do this, or think that this isn't possible, please let me know so I can start thinking of some other way to accomplish my goal. Any help is greatly appreciated!Thanks a lot for the looks.
Hank
