Defining a range of dates

[lathamkr]

Hello,

I am writing a macro to generate a pie chart from a growing database. I have written the below code to move all of the data entries from a specific month from the large database to another sheet to facilitate generating the chart.

My problem: In the userform I'm using to find and move the data I have a listbox offering the last 12 months as selections. In the loop function I built either I cannot get the value recognized as a date or the range of my selected date is botched.

Private Sub UserForm_Initialize()

'lbmonth fill    
With lbmonth        
.AddItem Format(Date, "MMMM")        
.AddItem Format(DateAdd("M", -1, Date), "MMMM")        
.AddItem Format(DateAdd("M", -2, Date), "MMMM")       
.AddItem Format(DateAdd("M", -3, Date), "MMMM")      
.AddItem Format(DateAdd("M", -4, Date), "MMMM")        
.AddItem Format(DateAdd("M", -5, Date), "MMMM")      
.AddItem Format(DateAdd("M", -6, Date), "MMMM")       
.AddItem Format(DateAdd("M", -7, Date), "MMMM")      
.AddItem Format(DateAdd("M", -8, Date), "MMMM")      
.AddItem Format(DateAdd("M", -9, Date), "MMMM")      
.AddItem Format(DateAdd("M", -10, Date), "MMMM")      
.AddItem Format(DateAdd("M", -11, Date), "MMMM")  
  
End With    

End Sub

Private Sub cbchartgenerator_Click()

Dim i, LastRow

LastRow = Sheets("Tracker").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Monthly Category").Range("A2:D500").ClearContents
For i = 2 To LastRow
If Sheets("Tracker").Cells(i, "A").Value = Range((DateSerial(Year(Me.lbmonth), Month(Me.lbmonth), 1)), (DateSerial(Year(Me.lbmonth), Month(Me.lbmonth) - 1, 0))) Then

Sheets("Tracker").Cells(i, "A").EntireRow.Copy Destination:=Sheets("Monthly Category").Range("A" & Rows.Count).End(xlUp).Offset(1)

End If

Next i

End Sub

Running the above code gives me a runtime error 13 as a "type mismatch"
I'm guessing it has to do with judging the value of the "A" column as a date, but I'm not really sure.

Any help would be greatly appreciated.

 

[AlphaFrog]

Try this...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cbchartgenerator_Click()
    
    [color=darkblue]Dim[/color] i      [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] dStart [color=darkblue]As[/color] [color=darkblue]Date[/color], dEnd [color=darkblue]As[/color] [color=darkblue]Date[/color]
    
    [color=darkblue]If[/color] Me.lbmonth.ListIndex = -1 [color=darkblue]Then[/color]
        MsgBox "Select a month in the listbox. ", , "Missing Selection"
    [color=darkblue]Else[/color]
        dStart = [color=darkblue]Date[/color] - Day(Date) + 1
        dStart = DateAdd("M", -lbmonth.ListIndex, dStart)
        dEnd = DateAdd("M", 1, dStart) - 1
    
        Sheets("Monthly Category").Range("A2:D500").ClearContents
        
        [color=darkblue]With[/color] Sheets("Tracker")
            [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] .Range("A" & Rows.Count).End(xlUp).Row
    
                [color=darkblue]If[/color] .Cells(i, "A").Value >= dStart And .Cells(i, "A").Value <= dEnd [color=darkblue]Then[/color]
    
                    .Rows(i).Copy Destination:=Sheets("Monthly Category").Range("A" & Rows.Count).End(xlUp).Offset(1)
    
                [color=darkblue]End[/color] [color=darkblue]If[/color]
    
            [color=darkblue]Next[/color] i
            MsgBox "Copy complete."
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]