CODEWITHSUNDEEP
vbaexcelfind
sharkyenergy
sharkyenergy

Reputation: 4173

Find if date is present in Range of cells (Excel VBA)

I have a cell F2 that contains a Date, and is formatted as Custom Date field displaying only day and month.

Then i have a Range of Cells C3;C60 that contains also Dates and is formatted as European Date field displaying dd/mm/yyyy

I am writing a VBA that makes a check on those fields, but it is not working. in the sample below the variable c can be any cell between F5 and F20.

I get RuntimeError 91. 

If Worksheets(1).Range("C3", "C60").Find(Worksheets(1).Cells(2, c.Column).Value) = True Then
        c.Value = "Whatever"        
Else

Upvotes: 1

Views: 5043

Answers (2)

FreeMan
FreeMan

Reputation: 5687

.Find() returns a range, your if statement is checking to see if it's TRUE. That will fail.

Dim rng as Range

Set rng = Worksheets(1).Range("C3:C60").Find(Worksheets(1).Cells(2, c.Column).Value)
if not rng is Nothing then
  c.Value = "Whatever"        
Else

Note the inclusion of Gary's answer

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

Substitute:

Range("C3:C60")

for

Range("C3", "C60")

There may be other problems.

Upvotes: 2

Related Questions