Find yesterdays date in range and return row number to be used in offset

[graphicgoose]

Hi,

I'm writing a macro that will import data from one workbook to another, based on yesterdays date. I have the import functionality working, and I've been playing all afternoon, but I can't find a way to find yesterdays date in a range and use that row number as the row offset value in this line:

Please Login or Register  to view this content.

Rather than having a set row offset value (in this case, 14) I would like to use the find function to lookup yesterdays date in range "B50:B80" in the worksheet I've designated as "sh1" and return the row number of the cell that has yesterdays date and use this value as the row offset value, replacing the hard coded 14 that is in there currently. The date values in the range are formatted as per Date Format.jpg.

My code is below:

Please Login or Register  to view this content.

Many thanks,
Dan

[AB33]

The find is looking at

Please Login or Register  to view this content.

[graphicgoose]

The find is looking at

Please Login or Register  to view this content.

Hi,

Sorry, no the find you have quoted is looking up other values that I need to match (which works as i require). My code, as it stands, imports the values i require and into the right section, but I require the column offset to be dynamic based on yesterday's date not the static 14 value that is currently in my code. So essentially i require some extra lines of code that will do as I've outlined in my original post.

[graphicgoose]

So I've been playing with a separate macro to attempt to find yesterday's date in the range and return a MsgBox with the row number to ensure it's finding the right cell. From my understanding I've written this new code correctly but for some reason it's not finding the date in the range so is returning the MsgBox saying "Incorrect!". I thought it might be something to do with date formatting, but I tried changing that also, to no avail. Can anyone see a reason why it's not finding the cell I require from Date_Range.jpg?

Please Login or Register  to view this content.

[Jim885]

Your code works for me, and found the row where yesterdays date was.

You may have to add a line of code that tells VBA your format is different.

Please Login or Register  to view this content.

As you step through your code, place the cursor over 'MyDate' to see its value. Hopefully that might help you troubleshoot the code.

[graphicgoose]

Your code works for me, and found the row where yesterdays date was.

You may have to add a line of code that tells VBA your format is different.

Please Login or Register  to view this content.

As you step through your code, place the cursor over 'MyDate' to see its value. Hopefully that might help you troubleshoot the code.

Thanks for the reply Jim. I have tried adding the format date line, again to no avail. Could the potential problem be that the cells with the dates in are linked to formulas in other cells?

[graphicgoose]

Ok, so turns out there was a format I hadn't tried. Set the format of dates in the range to the "*14/03/2001" format and it finds them no worries. Makes sense I guess, given the date function in VBA is using regional date formats and so is the cell now. Thanks for the replies guys.

Working code is:

Please Login or Register  to view this content.