Searching for dates in worksheet range from VBA

[The Gunslinger]

ok, i'm stuck again!

I have a line of code, that is supposed to simply check for the presence of a date in one worksheets cell, and then check to see if it's found in a column of cells in another worksheet
it sounds simple, and i thought i'd nailed it, but i keep getting a Run Time Error 13 - Type mismatch

here's the line with the issue

If Trim(ActiveSheet.Range("D5").Value) <> "" And Evaluate("COUNTIF(Sheet3!J18:J626," & CLng(ActiveSheet.Range("D5").Value) & ")") > 0 Then

for context purposes, this line of code then runs into a loop that removes all said dates from the second worksheet, and here's the full code, not bug checked yet as i can't get past the previous issue

    If Trim(ActiveSheet.Range("D5").Value) <> "" And Evaluate("COUNTIF(Sheet3!J18:J626," & CLng(ActiveSheet.Range("D5").Value) & ")") > 0 Then
        With Sheet3.Range("J18:J626")
            Set c = .Find(CLng(ActiveSheet.Range("D5").Value), LookIn:=xlValues)
            If Not c Is Nothing Then
                firstAddress = c.Address
                Do
                    c.Offset(0, 1).Value = ""
                    c.Offset(0, 3).Value = ""
                    c.Offset(0, 1).Locked = False
                    c.Offset(0, 3).Locked = False
                    c.Value = ""
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
        End With
    End If

i'm obviously going to keep working on this but it's driving me up the wall, because obviously i've missed something simple, but can't see it, any help would be appreciated

 

[wideboydixon]

The logical answer is that whatever is in D5 can't be converted to a Long - that would generate the type mismatch. I notice you're trimming in the first check but not when converting to a long. If you go to the immediate window (Ctrl+G) and type the following, what does it show?

? """" & ActiveSheet.Range("D5").Value & """"

WBD

 

[The Gunslinger]

oh snap....... do i feel stupid!!

I fixed it, and it was as simple as i thought..... the problem was this bit.....

"COUNTIF(Sheet3!J18:J626,"


i referenced the sheet number, and NOT the sheet name...... Doh!

 

[The Gunslinger]

The logical answer is that whatever is in D5 can't be converted to a Long - that would generate the type mismatch. I notice you're trimming in the first check but not when converting to a long. If you go to the immediate window (Ctrl+G) and type the following, what does it show?

? """" & ActiveSheet.Range("D5").Value & """"

WBD

the first check with the TRIM function is simply to check i have an entry in there, and not a random space, at this point i'm not too worried about checking if it's a valid date, because the next part of the AND statement compares the value in D5 converted to Long with the column in the other worksheet.

the value coming out of D5 is either "" (as in blank) or in my test case 43161 which is the numerical value for the date in the cell, and i then used that in the find function to check for matches.


I need to test it further but thanks for the help.

And in case anyone else is hitting a similar error...... i took the problem code, in this case the countif, and built that part of the formula in a spare cell in the worksheet, that highlighted my error ! sometiumes when transfering worksheet functions to vba, it's an idea to make sure they work on a worksheet first! LOL

 

[The Gunslinger]

further testing is proving that the rest of the code doesnt work either

I've removed the c.value = "" line as that was a typo, it has a formula in there, which will return "" anyway as it's target sheet/value will get removed.


it looks like i have an issue with the set line, because it's not stepping into the loop, always saying C = Nothing

 

[The Gunslinger]

ok, i figured it out, renamed a couple of things and removed the "with..." method, as there was no benefit to it.

my basic problem, was that the CountIF code was happy searching using the numeric value that represented the date to search for, and compare it to the results of formulas in cells formatted to then display them as a readable date string.
unfortunately, the .find turned out not to be happy with that, and was looking for the numeric value in a list of dates already formatted, so for that to work i had to swap the numeric value into the exact same format as the cell was set to display, this then worked

here's the new code in case it's of any help to anyone in the future.....

    Dim FoundDate As Range
    Dim FirstAddress As String
    Dim SrchDate As String

    If Trim(ActiveSheet.Range("D5").Value) <> "" And Evaluate("COUNTIF(Signatures!J12:J626," & CLng(ActiveSheet.Range("D5").Value) & ")") > 0 Then
        SrchDate = Format(ActiveSheet.Range("D5").Value, "d mmm yy")                ' need to switch date to search to the same format as dates being examined
        Set FoundDate = Sheet3.Range("J12:J626").Find(SrchDate, LookIn:=xlValues)
        If Not FoundDate Is Nothing Then
            FirstAddress = FoundDate.Address
            Do
                Sheet3.Range(FoundDate.Address).Offset(0, 1).ClearContents
                Sheet3.Range(FoundDate.Address).Offset(0, 3).ClearContents
                Sheet3.Range(FoundDate.Address).Offset(0, 1).Locked = False
                Sheet3.Range(FoundDate.Address).Offset(0, 3).Locked = False
                Set FoundDate = Sheet3.Range("J12:J626").FindNext(After:=FoundDate)
            Loop While Not FoundDate Is Nothing And FoundDate.Address <> FirstAddress
        End If
    End If

i've come to the conclusion i hate working with dates