[SOLVED] VBA code to generate report based on combobox variables

VBA code to generate report based on combobox variables

I have a userform that collects data and places it in the Data sheet. I want to add a userform that allows the user to run reports on various criteria and the report to appear in the Reports sheet. I've almost got it working based on two comboboxes (cmdmonth and cmdstatus) but its only working on an AND basis (i.e. it'll report all data that matches both the month and status) but I want it to report all data that matches either option (i.e. month or status). If I leave either combobox empty no results are returned.

The following code works when both comboboxes are selected and the data matches both, but if either box is empty no results are returned. Eventually there will be more combobox options for the user to select to generate reports (i.e. user, project, customer etc) so i don't want to use every combobox for every report.

Private Sub cmdrun_Click()
    Dim sdsheet As Worksheet, grsheet As Worksheet
    Dim sdlr As Long, grlr As Long, y As Long, x As Long

    Set sdsheet = ThisWorkbook.Sheets("Data")
    Set grsheet = ThisWorkbook.Sheets("Report")
    Dim match As Boolean
    match = False

    sdlr = Application.Max(sdsheet.Cells(Rows.Count, 4).End(xlUp).Row, 2)
    grlr = Application.Max(grsheet.Cells(Rows.Count, 4).End(xlUp).Row, 2)
    
    ' starting row
    y = 5
    
    ' clear data from grlr
     grsheet.Range("A5:k5000" & grlr).ClearContents
 
    'month
    For x = 5 To sdlr

        If Me.cmbmonth = "All" Or sdsheet.Cells(x, 2) = Me.cmbmonth Then
        If Me.cmbstatus = "All" Or sdsheet.Cells(x, 11) = Me.cmbstatus Then

            grsheet.Cells(y, 1).Resize(1, 11).Value = sdsheet.Cells(x, 1).Resize(1, 11).Value
            y = y + 1
            match = True

        End If
        End If
        
    Next

    grsheet.Activate

End Sub

P.s. I'm quite new to VBA coding and teaching myself so apologies if my code is a bit messy!

Thanks David

Solution

I am about as new as you are to VBA coding, but your question is a simple logical question about AND and OR operators. Currently you have two If functions nested, so you are checking that if function 1 is true AND if function 2 is true, if either isn´t, your code opens an empty report sheet. Simply combine both if functions into a longer if Or and your problem should be solved

 If Me.cmbmonth = "All" Or sdsheet.Cells(x, 2) = Me.cmbmonth Or Me.cmbstatus = "All" Or sdsheet.Cells(x, 11) = Me.cmbstatus Then

This should check if atleast one of the 4 conditions is true before returning an answer. If you end up having more comboboxes, you need to just extend the If or function accordingly