Error In Finding Date In Another Range

Re: Error In Finding Date In Another Range

Dim s1, s2 as worksheet
dim tdy as range
Dim today as long


set s1 = sheets("Sheet1")
set s2 = sheets("Sheet2")


today = s1.range("C1")


Set tdy = s2.range("A1:A65536").find(today, lookin:=xlvalues)
If Not tdy is Nothing then tdy.Activate

Re: Error In Finding Date In Another Range

Quote from tardy

Is there a way for the macro to pickup this date format instead of the string/formula in C1?

Have you tried:

Dim today As Date

?
What exactly you want to find? TODAY()? Or WORKDAY?

Re: Error In Finding Date In Another Range

Quote from tardy

Hi Guys,

This is really dumb of me but here goes...

I have a formula =WORKDAY(TODAY(),-1) which will give me 10 Apr 07 in C1of sheet1 today.

I am trying to find the cell in column A of sheet2 which has the date value equal to C1.

However, my below macro keeps telling me object variable not set at the last line.. can someone enlighten me why?

Code

Dim s1, s2 as worksheet
dim tdy as range
Dim today as long


set s1 = sheets("Sheet1")
set s2 = sheets("Sheet2")


today = s1.range("C1")


Set tdy = s2.range("A1:A65536").find(today, lookin:=xlvalues)
tdy.Activate

Display More

Cheers
Lloyd

Display More

try this

Dim s1 As Worksheet
    Dim s2 As Worksheet
    Dim tdy As Range
    Dim today
    Set s1 = Sheets(1)
    Set s2 = Sheets(1)
    today = s1.Range("C4").Value
    Set tdy = s2.Range("A:A").Find(WorksheetFunction.Text(today, "dd/mm/yy"), s2.Range("a:a").Cells(10, 1), LookIn:=xlValues, lookat:=xlWhole)
    Cells(tdy.Row - 1, 1).Select

cheers!!
senthil

Re: Error In Finding Date In Another Range

Quote from tardy

Is there a way for the macro to pickup this date format instead of the string/formula in C1?

Something like that?:

Sub sdata()
Dim s1, s2 As Worksheet
Dim tdy As Range
Dim today As Long
 
Set s2 = Sheets(2)


today = Sheets(1).Range("C1")


Set tdy = s2.Range("A1:A65536").Find(CDate(today))
If Not tdy Is Nothing Then Application.Goto tdy
End Sub

Re: Error In Finding Date In Another Range

Quote from tardy

HiHi theOkk

Went out of office yesterday...

ok that kinda works but i need to change the date format from 4-digit years (dd/mm/yyyy) to 2-digit years (dd/mm/yy) instead... Have you any idea how to do that?

Or is there another method to specify the macro to look for the value of C1 in terms of serial value?

Display More

Can you give us an example of your file?

Re: Error In Finding Date In Another Range

Unlike some other languages, in this declaration,

Quote from the Okk

Code

Dim s1, s2 As worksheet

s1 is declared as a variant.

Re: Error In Finding Date In Another Range

Hi,

Finding dates can be a real pain in excel sometimes.

This code below is tested and works. However it is important that the date format in the code matches the date format in the search range on the worksheet.

Sub sdata()
    Dim SrchRng As Range
    Dim tdy As Range
    Dim today As Date


    Set SrchRng = Sheets(2).Range("A:A")
    today = Format(CDate(Sheets(1).Range("C1").Value), "DD/MM/YY")
    Set tdy = SrchRng.Find(What:=today, LookIn:=xlValues)
    If Not tdy Is Nothing Then Application.Goto tdy
End Sub

Good Luck.

Re: Error In Finding Date In Another Range

Quote from tardy

it is really such a pain in the a**!!!!!

I will try that out later reafidy... thanks a million!

shg: So it is advisable to seperate the two declarations?

Regards,
Lloyd

Display More

Yes you need:

Dim s1 as worksheet, s2 as worksheet

Re: Error In Finding Date In Another Range

There is only one date in the sheet, why dont you provide a proper example with lots of dates so we can see the formats there is nothing even in c1 which is the date you are searching for...

Re: Error In Finding Date In Another Range

Ignore

Re: Error In Finding Date In Another Range

I have combined the files to give to lots of code.

2 buttons you can see both code.

HTH

Re: Error In Finding Date In Another Range

Did you see my last edit I modifed the code again?

I wouldnt recommend the grunt method. It formats an entire column so excel thinks all the column contains data and takes up more memory.

See Here - Ozgrid Good Spreadsheet Design

HTH