return the row number based on contents of cell

[sedgely]

i am trying to return the row number based on the contents in column A. basically i need to search down column A until i find yesterdays date and return the corresponding row number. so far i have got...

Function export()

Dim rowNum
Dim filename
Dim date1
Dim rownum As Long

    date1 = Date - 1
rownum = Worksheets("sheet1").Range("a:a").Find(date1).Row

'some more code...

End Function

i'm getting frustrated now cos i had this working earlier today but can't remember how . i knonw the problem lies with

rownum = Worksheets("sheet1").Range("a:a").Find(date1).Row

but i just can't see what it is.

Don't laugh at me for being dumb, it's been one of those days!

Cheers, Craig
Si fractum non sit, noli id reficere

 

[xlbo]

what error are you getting ?

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

 

[xlbo]

does this work ?

dim fCell as range, TheRow as long
Set fCell = sheets("Sheet1").columns("A").find(format(Date - 1,Range("A2").numberformat))
TheRow = fCell.Row


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

 

[kodr]

I tried your code and it works for me. The only thing I did differntly was to declare date1 as a date type:

Dim date1 as Date

 

[sedgely]

thx for both replies i have tried declaring date1 as date and have tried your alternative code Geoff, but am still getting "object variable or with block variable not set"

Cheers, Craig
Si fractum non sit, noli id reficere

 

[kodr]

I get that error message when it can't find the date it's looking for. Do you need to add some error handling?

 

[sedgely]

Mmmm that's strange, the date is definitely in the range. Will look at it more tomorrow, i've had enough today - time to go home. Thanks for your suggestions, will let you know if i sort it (or not)

Cheers, Craig
Si fractum non sit, noli id reficere

 

[xlbo]

Craig - that's why I used the numberformat of (what I guessed was) the 1st date in the search column - you have to use the right formats when searching for dates

Other casuse may be if you don't have exact dates - try formatting your column as dd/mm/yyyy hh:mm:ss to see if there's any extra hours that have crept into your values

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

 

[firefytr]

Try ..

Function export()
    
    Dim ws As Worksheet, rngLook As Range, strFormat As String
    Dim lngRowNum As Long, strFileName As String, Date1 As Date
    
    Date1 = Date - 1
    Set ws = Worksheets("sheet1")
    Set rngLook = ws.Range("A:A")
    strFormat = "mm-dd-yy" 'or can use another cells format .. ws.Range("A2").NumberFormat
    lngRowNum = rngLook.Find(Format(Date1, strFormat)).Row
    
    'some more code...
    
End Function

It's best to declare what type of variable you have; if you leave it blank, it will dimension it as a Variant, wasting resources.

HTH

-----------
Regards,
Zack Barresse

 

[sedgely]

Guys
thanks for all your help, it's amazing what a nights sleep can do.
The problem was not with the code but the fact that Sheet1 was protected. DOH!
Didn't think that would make any difference seeing as i wasn't trying to alter anything on the sheet but it obviously does. final code i ended up with was

Function export()
Dim ws As Worksheet, rngLook As Range, strFormat As String
Dim lngRowNum As Long, strFileName As String, date1 As Date

date1 = Date - 1
Set ws = Worksheets("sheet1")
Set rngLook = ws.Range("A:A")
ws.Unprotect ("xxx")
strFormat = ws.Range("a7").NumberFormat
longrowNum = rngLook.Find(Format(date1, strFormat)).Row

'some more code...

ws.Protect ("xxx")

End Function

Thanks again

Cheers, Craig
Si fractum non sit, noli id reficere