Search userform function

Hi Guys,

I am trying unsuccessfully to get a search between 2 dates working on a userform

I have been working with code form another thread

And I am getting error every now and again when I run it

Dim i As Long, j As Long, ws As Worksheet, Z
If IsDate(Accounting.TextDate1) And IsDate(Accounting.TextDate2) Then
Set ws = Sheets("Transactions Summary")
On Error GoTo 0
 'Cells.AutoFilter
 If Sheets("Transactions Summary").FilterMode = True Then Sheets("Transactions Summary").ShowAllData

SDate = CDate(Accounting.TextDate1)
EDate = CDate(Accounting.TextDate2)
    i = 2
    Do Until ws.Cells(i, 2) >= SDate
    i = i + 1
    Loop
        j = ws.Range("B" & Rows.Count).End(xlUp).Row
        Do Until j = 1 Or ws.Cells(j, 2) <= EDate: j = j - 1
        Loop
                            If j = 1 Then
        MsgBox "Date range does not exist in this data"
        Exit Sub
        End If
        With ws.Range(Cells(i, 1), Cells(j, 2))
        
        .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        
        Z = ws.Range(Cells(i, 1), Cells(j, 2)).SpecialCells(xlCellTypeVisible)
        Accounting.ListBox1.List = Z: ws.Rows.Hidden = False: End With
Else
        MsgBox "Please enter both dates"
End If

End Sub

sometimes it works and other times I get "Run-time Error '1004' Method 'Range out of object' worksheet failed"

I thought I might be down to a pre existing filter and add a clear filter but it still bombs out on this line

With ws.Range(Cells(i, 1), Cells(j, 2))

the data it is pulling from the 'Transactions Summary' table looks like this

Data_Concat	Date
€10 - Voucher	01/01/15
€50 - Purchase	01/01/16

Can anyone help me please ?

TIA
Sarah

It's probably that you have not fully qualified all the ranges with sheet references

With ws.Range(ws.Cells(i, 1), ws.Cells(j, 2))

Hi there,

Try using:

With Range(ws.Cells(i, 1), ws.Cells(j, 2))

Without fully qualified references, "Cells(i, 1)" and "Cells(j, 2)" will be taken as applying to whichever worksheet happens to be active when the macro reaches that point in its execution.


Hope this helps - please let me know how you get on.

Regards,

Greg M (fellow Dubliner )

Sarah - wherever you have a reference to a Range or Cells you need to add a sheet reference (put the sheet name or variable name in front). You have done this in most places but below I have filled in a few gaps

Dim i As Long, j As Long, ws As Worksheet, Z
If IsDate(Accounting.TextDate1) And IsDate(Accounting.TextDate2) Then
Set ws = Sheets("Transactions Summary")
On Error GoTo 0
 'Cells.AutoFilter
 If Sheets("Transactions Summary").FilterMode = True Then Sheets("Transactions Summary").ShowAllData

SDate = CDate(Accounting.TextDate1)
EDate = CDate(Accounting.TextDate2)
    i = 2
    Do Until ws.Cells(i, 2) >= SDate
    i = i + 1
    Loop
        j = ws.Range("B" & Rows.Count).End(xlUp).Row
        Do Until j = 1 Or ws.Cells(j, 2) <= EDate: j = j - 1
        Loop
                            If j = 1 Then
        MsgBox "Date range does not exist in this data"
        Exit Sub
        End If
        With ws.Range(ws.Cells(i, 1), ws.Cells(j, 2))
        
        .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        
        Z = ws.Range(ws.Cells(i, 1),ws.Cells(j, 2)).SpecialCells(xlCellTypeVisible)
        Accounting.ListBox1.List = Z: ws.Rows.Hidden = False: End With
Else
        MsgBox "Please enter both dates"
End If

End Sub

OMG THANK YOU ... I have been trying to get that going for hours ...

P.s did I mention Thanks

My pleasure.