VBA Date Range - Overflow problem

[JessM]

Hey all,

Wondering if anyone can help me.

Basically I have a number of sheets in a workbook that I need my VBA to search through for any rows that contain a date within a range, copy that information and paste it into a specified sheet. I have it working for a single date using VBA but cannot work out how to get it to work a range specified by the user (start and end dates).

Here is a pastebin for my code: [VisualBasic] VBA Overflow Daterange - Pastebin.com

I am getting an overflow error on line "nxtRw = nxtRw + .SpecialCells(xlCellTypeVisible).Count - 1"

 

[Fluff]

Hia & welcome to the board
That row is adding up all the visible cells rather than the rows. try something like

NxtRw = NxtRw + Sheets(shtNum).cells.Find("*", after:=Sheets(shtNum).Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row.Offset(1)

 

[steve the fish]

Hi. There are a couple of problems that are initially apparent. You are qualifying your ranges with sheet names most of the time but didnt in this block:

If Sheets("GRN-Date Search").Range("J3") = "" Or Sheets("GRN-Date Search").Range("J4") = "" Then
    MsgBox "You must enter a date range to search"
    Sheets("GRN-Date Search").Select
    Range("J3").Select
    Exit Sub
End If

Then for nxtRw id go:

nxtRw = Sheets("GRN-Date Search").Cells(Rows.Count, 1).End(xlUp).Row + 1

 

[JessM]

Thanks so much for your help guys! I have it working now. Man that has been bugging me.

 

[Fluff]

Glad we could help