Confusion on .Find

Martin Perera

Board Regular
Joined
Jan 17, 2005
Messages
147
Hi guys

I am somewhat confused, I seem to be having difficulty passing a variable to .Find.

this bit of code works as it should

Code:
Sub FindDate()
Dim dDate As Date
Dim c As Range
'need to pass the date to sub as a variable.. how what sort?

dDate = Range("d1").Value
With Worksheets(1).Range("A10:AG10")  'range with dates
   Set c = .Find("20/03/05")  'find this date in row 10
    Range(c.Offset(1, 0), c.Offset(33, 27)).Select  'range just below selected date
      
End With

End Sub

however when I try to pass the date as a variable it finds the last entry in the row then sets the range.


Code:
Sub FindDate()
Dim dDate As Date
Dim c As Range
'need to pass the date to sub as a variable.. how what sort?

dDate = Range("d1").Value
With Worksheets(1).Range("A10:AG10")  'range with dates
   Set c = .Find(dDate)  'find this date in row 10 goes to end of row?
    Range(c.Offset(1, 0), c.Offset(33, 27)).Select  'range just below selected date
      
End With

End Sub

I do not understand why this is happening any one got any ideas.....

cheers

martin
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Tip:
When U use Find method, always define all the properties, otherwise excel will use the last ones used
 
Upvote 0
Hi Martin,

The Find method can sometimes be a little tricky to use with dates. Try using DateValue with your variable, like this :

Set c = .Find(DateValue(dDate))

Does that help?
 
Upvote 0
hi Richie

using the datevalue did help to find the date but now it throws up an

object variable or with block variable not set

and highlights this row

Code:
Range(c.Offset(1, 0), c.Offset(33, 27)).Select  'range just below selected date

which seems to indicate that the code thinks that c variable isnt set.????

however it has been defined as a Range and set in this line

Code:
Set c = .Find(DateValue(dDate))

or at least i thought so???

could there be another reason for this error?

cheers
martin
 
Upvote 0
martin

The reason for this could be because the date hasn't been found.

Like Richie said dates can be notoriously difficult when using the .Find method.

Another problem with the .Find method is if you don't supply all the arguments then it will default to those used previously.
 
Upvote 0
Hi Norrie

OK so if its not finding the date even though it is there, is there another way of finding a date in a range? all I need is the cell reference so that i can offset the range i wish to copy and paste

otherwise

is there some place i can get all the arguments neccessay for the .find method....

cheers

martin
 
Upvote 0
martin

1 You could loop through the range, but obviously that may be time consuming.

2 Best way is to turn on the macro recorder and do it manually. that should generate code that you can use for your find.

To actually just get the list of arguments, select Find in the VBA editor and hit F1. That should open up the relevant page in help.
 
Upvote 0
Find looks for the value of a cell, not how it may be formatted. When you use .Find("20/03/05") and it works, suggests to me that the values saved in the range you are looking at are not dates at all, but saved as text, while D1 is an actual date.

If the range was saved as actual dates then this would fail because a dates value is actually a number, so the numerical value of 20/3/05 is 38431, and thats what find is looking for when you used the ddate variable.

If this is the case then change Dim dDate as Date to Dim dDate as string and amend dDate = Range("d1").Value to dDate = Range("d1").Text.
 
Upvote 0
got it sussed guys

should have been using

Code:
Set c = .Find _
   (what:=DateValue(dDate), LookIn:=xlFormulas)

not .....

Code:
Set c = .Find _
   (what:=DateValue(dDate), LookIn:=xlValues)

this works okay, only problem is most of my dates are via formulas...
eg

A1....................B1
20/03/2005 =A1+1

as it is looking in formulas it couldnt find the date.....:0

just got to change all my dates to true dates,


cheers

martin
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top