Combining VBA

[longytravel]

I have the following code.

The first button searches for and returns data between two dates

The second searches for data based on project name

I would like to now combine the two and search by project name as well as dates if the user chooses to put in two dates. If they dont it simply returns the info based on project name (as it does currently)

Is that possible?

How would it fit into my exisiting code?

As ever greatfull for any help!

 

[Damon Ostrander]

Hi longytravel,

Yes of course it is possible. But it is impossible to tell you how to modify your code without seeing it. Can you post it, or better yet post your workbook somewhere on the web where we can download it?

Damon

 

[longytravel]

Hi Damon!

Sorry.........i'm an idiot!!

Here is the code.........not sure why i didnt post it last time?????

I have also added a third search which uses a second combo box - RAG status (red, Amber, Green).

Again, would be good to combine the date and the detail in the two different combo boxes?

So -

Search 1 - Just searchs via date
Search 2 - Just searches by Project
Search 3 - Just searchs by RAG status

I have all this

Can i:

Search 4 - Search for a project between 2 dates?
Search 5 - Search for a RAG status between 2 dates?
Search 6 - Search for a certain RAG and Project name between 2 dates???????

Look forward to your views on this!!! I am pretty rubbish at all this so really simple answers would be appriciated!!

Cheers

Private Sub CommandButton1_Click()
    Date1 = CLng(DateValue(Me.Date10.Text))
    Date2 = CLng(DateValue(Me.Date20.Text))
    With Sheets("data")
        lr = .Cells(Rows.Count, "B").End(xlUp).Row
        .Range("B1").AutoFilter
        .Range("B1").AutoFilter Field:=2, Criteria1:=">=" & Date1, Operator:=xlAnd, _
            Criteria2:="<=" & Date2
        .Range("A1:I" & lr).SpecialCells(xlCellTypeVisible).Copy Sheets("reports").Range("B2")
        .AutoFilterMode = False
    End With
    
    Range("A:A").ColumnWidth = 5
    Columns("B:C").ColumnWidth = 9
    Columns("D:H").ColumnWidth = 25
    Columns("I:J").ColumnWidth = 9
    
        Cells.Select
    With Selection
        .VerticalAlignment = xlCenter
    End With
    Columns("D:H").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
    End With
    Range("B2").Select
    
    
End Sub
    
   

Private Sub CommandButton3_Click()
    With Sheets("data")
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("A1").AutoFilter
        .Range("A1").AutoFilter Field:=1, Criteria1:=Me.ComboBox1
        .Range("A1:I" & lr).SpecialCells(xlCellTypeVisible).Copy Sheets("reports").Range("B2")
        .AutoFilterMode = False
    End With
    
    Range("A:A").ColumnWidth = 5
    Columns("B:C").ColumnWidth = 9
    Columns("D:H").ColumnWidth = 25
    Columns("I:J").ColumnWidth = 9
    
        Cells.Select
    With Selection
        .VerticalAlignment = xlCenter
    End With
    Columns("D:H").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
    End With
    Range("B2").Select
    
End Sub

Private Sub CommandButton7_Click()
    With Sheets("data")
        lr = .Cells(Rows.Count, "I").End(xlUp).Row
        .Range("I1").AutoFilter
        .Range("I1").AutoFilter Field:=9, Criteria1:=Me.ComboBox2
        .Range("A1:I" & lr).SpecialCells(xlCellTypeVisible).Copy Sheets("reports").Range("B2")
        .AutoFilterMode = False
    End With
    
     Range("A:A").ColumnWidth = 5
    Columns("B:C").ColumnWidth = 9
    Columns("D:H").ColumnWidth = 25
    Columns("I:J").ColumnWidth = 9
    
        Cells.Select
    With Selection
        .VerticalAlignment = xlCenter
    End With
    Columns("D:H").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
    End With
    Range("B2").Select
    
    
End Sub

Private Sub Date10_AfterUpdate()
If Not IsDate(Me.Date10.Value) Then
    Me.Date10.Text = ""
    MsgBox "Please Type a Valid Date!"
    Exit Sub
End If
End Sub
Private Sub Date20_AfterUpdate()
If Not IsDate(Me.Date20.Value) Then
    Me.Date20.Text = ""
    MsgBox "Please Type A Valid Date!"
    Exit Sub
End If
End Sub