Excel VBA .Find Function not finding a date

Question

I am using the following VBA code to select the first instance of each data in a column and copy it to a new column to the left.

For a reason I do not understand, the .Find method does not find any of the dates in my worksheet.

Dates are in the format 03/10/2017 17:05:00

However after closing the macro editor (having used step-into to debug) I can press Ctrl + F and Return to cycle through the dates as expected, the macro even fills the window in!

So I must be missing something, but no errors are being thrown.

c remains undefined.

count = An integer representing the day of the month to cycle through the dates

c = The range where the search string is found (or should be in this case)

d = The kill switch, when the loop eventually causes the script to find the first date that has already been copied to the left d should become 1 and end the loop. Untested due to this error.

cell = used purely to fill remaining cells in the new column with #N/A

As you can probably tell I am very much an amateur here, I welcome and appreciate any assistance or feedback.

Sub LabelMaker()
'
' LabelMaker Macro
'
Dim count As Integer
count = 2
Dim c As Range
Dim s As String
Dim d As Integer
d = 0
Dim cell As Range
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
    Columns("C:C").Insert Shift:=xlToRight
    Range("C1") = "Labels"
    Do
        s = CStr(count) & "/10/2017"
        Set c = .Range("D:D").Find(s, LookIn:=xlValues, LookAt:=xlPart)
            If Not c Is Nothing Then
                If IsEmpty((c.Offset(rowOffset:=0, columnOffset:=-1))) Then
                    c.Copy _
                        Destination:=c.Offset(rowOffset:=0, columnOffset:=-1)
                Else
                    d = 1
                End If
            End If
            count = count + 1
            If count = 32 Then
                count = 1
            End If
    Loop While d = 0
    For Each cell In Range("C:C")
        If IsEmpty(cell) Then
            cell = "#N/A"
        End If
    Next
End With
'
End Sub

YowE3K · Accepted Answer · 2017-11-29 01:58:53Z

2

Your spreadsheet probably contains actual dates, rather than just text that looks like a date, so convert your string to a date:

Set c = .Range("D:D").Find(CDate(s), LookIn:=xlValues, LookAt:=xlPart)

answered Nov 29, 2017 at 1:58

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Unencoded Over a year ago

Damn I was so close... Thank you very much.

YowE3K Over a year ago

@Sam3000 And, when you decide that you don't like having a million cells containing "#N/A", you might consider changing For Each cell In Range("C:C") to For Each cell In .Range("C1:C" & .Cells(.Rows.Count, "D").End(xlUp).Row) ;-)

Collectives™ on Stack Overflow

Excel VBA .Find Function not finding a date

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related