VBA - Finding a date

Question

I have 2 workbooks book1 & book2. Both book1 and book2 have first row as dates

e.g. Book1
A       B       C
Jan-13       Feb-13      Mar-13
1                 4
2                 5
3                 6

Book2
A       B       C
Jan-13       Feb-13      Mar-13
1                 4                 3
5                 7                 6
9                 8                 1

The idea is to select a date from Book 1 and find if the date exists in book2 and copy the contents of book 2 column into book1 column

e.g. if I select Mar-13 in book 1, I should be able to find Mar-13 in book 2 and copy C3 to C5 from book2 to C3 to C5 of book1.

I am struggling with the find command in vba, I am using something like this to find

With ThisWorkbook.ActiveSheet.Range("A1:Z1")

    Set rngSMonthYr = .Find(What:=dtMonthYr, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext)
End With

But it isn't working at all.

David Zemens · Accepted Answer · 2013-10-02 13:42:49Z

1

Make sure you declare your variable as a Date type, and make sure that you're including the "day" portion of the date.

Dim dtMonthYr As Date
dtMonthYr = #1/1/2013#

Even when you enter a value like 1/2013 in to a cell, Excel stores this as a Long date. This is tested and it works.

Sub TestFindDate()
Dim rngSMonthYr As Range
Dim dtMonthYr As Date
Dim wsFind as Worksheet


dtMonthYr = #1/1/2013#   

Set wsFind = ThisWorkbook.Sheets("Sheet2") '## Modify as needed

    With wsFind.Range("A1:Z1")
    Set rngSMonthYr = .Find(What:=dtMonthYr, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext)
    End With

If rngSMonthYr Is Nothing Then
    Debug.Print dtMonthYr & " not found"
Else:
    Debug.Print rngSMonthYr.Address
End If

Emd Sub

edited Oct 2, 2013 at 13:42

answered Oct 2, 2013 at 1:45

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

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

2 Comments

Aaron Over a year ago

I am using 2 different sheets, select date from sheet 1, and check if it exists in sheet 2, the date format is MMM-YY, but once I get the value it becomes DD/MM/YYYY, but if I get the text it stays MMM-YY,but the check actually returns address from sheet 1 which I need it from sheet 2

David Zemens Over a year ago

See update, I made a change to force the .Find operation on a specific worksheet.

Collectives™ on Stack Overflow

VBA - 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