find text value problem 2

[robcarr]

Hi,

I have a row on sheet that displays dates but has formulas as the values in the cells.

I use this coding to try and find the data

Range("a1").Activate

myrange = Range("A1:bx1")
myrange.Find(What:=ddate, After:=Range("a1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate

this works fine when ddate is the value in the cell as well as the text, but if the value is a fomula like =b1+1, it cant find the date. how can i get round this.

I have tried Xlbo's way and it did not work - it says object required

thanks

Hope this is of use, Rob.

 

[SkipVought]

Hi,

Could you demonstrate what you mean with an example of data? I tried your code with minor adjustments and it worked for me.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[robcarr]

cell b1 has the date in the format dd/mm/yy
cell d1 has the formula =b1+1, formatted to show as a date.

every other column has the formula like d1, so for cell f1 it would be =d1+1, etc all the way upto cell br1 which has =bp1+1

what I am trying to do is find the date I am looking for.

hope this helps.


Hope this is of use, Rob.

 

[SkipVought]

Again, I did just as you prescribed and the find works.

Is your RANGE correct?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[robcarr]

my variable for searching is a defined as a date

dim ddate as date

and it says object required, i have this

Range("a1").Activate

Set myrange = Range("A1:bx1")
        myrange.Find(What:=ddate, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate

if i change activecell to range("a1"), it still says the same, cant find the value.

Rob.

Hope this is of use, Rob.

 

[rmikesmith]

Rob,

Like Skip, I was able to use essentially your code with success. I put a date into cell A1 (10/08/2007). In cell B1 I entered the formula =A1+1 then copied this over several additional columns. Here is the exact code I used:

Sub FindDate()
Dim myrange As Range
Dim ddate As Date

   ddate = #10/13/2007#

Set myrange = Range("A1:bx1")
        myrange.Find(What:=ddate, After:=Range("a1"), LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False).Activate

End Sub

FYI. My dates are in mm/dd/yyyy format (although no explicit formatting was set).

Regards,
Mike

 

[robcarr]

this was odd, but now works,
the only difference was I declared myrange

that was the only difference on mine and both of your coding suggestions.

oh well works now, saves a bit of time and makes the reporting more accurate.

thanks for your help.

Hope this is of use, Rob.

 

[rmikesmith]

Rob,

Just reproduced your error. Apparently specific formatting does have an effect on ability to "find". If no instance is found, the Find method returns Nothing and because your code is attempting to Activate a range that is Nothing, you get the Object Required error.

I'll take a closer look at this.


Regards,
Mike

 

[robcarr]

jsut ran the coding through and it found the data okay, with myrange declared,

Hope this is of use, Rob.

 

[rmikesmith]

Just to follow up and reiterate my findings:

My original test used default formatting of the dates I entered and/or that were returned from the formulas. This format was of the form mm/dd/yyyy. The date I assigned in my code (see above) was explicitly given in the same format. The code found this date in the search range, as you would expect. When I changed the formatting of the cell containing this searched-for date to mm/dd/yy and re-ran the code, it failed to find the date. One way around this is search for a string value generated by applying the Format function to the date variable, using the same formatting as the searched-in cells. Here is my modified code that successfully found the formatted date:

Sub FindDate()
Dim myrange As Range
Dim rngFound As Range
Dim ddate As Date
Dim sDate As String

   ddate = #10/13/2007#
   sDate = Format(ddate, "mm/dd/yy")

   Set myrange = Range("A1:bx1")
   Set rngFound = myrange.Find(What:=sDate, After:=Range("a1"), LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False)
   If Not rngFound Is Nothing Then
     MsgBox "Date located in cell " & rngFound.Address
   Else
     MsgBox "Date not found."
   End If
   
End Sub

Hope this helps
Mike

 

[xlbo]

lol - that is what I posted in the original request in the MSOffice forum ;-)



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[rmikesmith]

Geoff,

I gotta get out more


Regards,
Mike

 

[xlbo]

admitedly, I posted it as a code snippet and didn't really explain why it may or may not work - been a bit busy for full explanations today !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question