If column has entry, then insert date from top

[tarmand]

I need help. I have a report that I keep forgetting to change the week ending date. I need to tell the date cell to look in rows 8 through 75 and find the farthest column that has an amount, then grab the date from row 6 of that column and insert it in the date cell (J7).

Essentially, I have this spreadsheet that lists projects by row and every week, I update the expenditures over to the right (April 16 information was in column "AM"). Some projects will have expenditures, and some won't. But I need the date to show the most recent week ending date entered.

Can someone help me with this?

 

[AlphaFrog]

Here's a custom User Defined Function (UDF) that should do what you want.

To install the UDF...

• Alt+F11 to open the VBA editor
• From the VBA menu, select Insert\Module
• Paste the code below in the VBA edit window
• Back in Excel, enter this formula in cell J7

=most_recent_week_ending_date(8:75)

8:75 are the rows to check for the last used column

Function most_recent_week_ending_date(rng As Range)
    Dim LastCol As Long
    LastCol = rng.Find("*", , , , xlByColumns, xlPrevious).Column
    most_recent_week_ending_date = Cells(6, LastCol).Value
End Function

 

[tarmand]

Thanks for your help. But, something is off. The date should show up as 04/16/11 and it is showing 10/01/11. I suck at macros, so I have no idea what your macro is telling it to do. But, it should be looking for the last colum (rows 8 to 75) that has data in it and grab the date from row 6 of that column. Is that what it says to do?

 

[tarmand]

Nevermind. Someone had done some calculating in that column. When I deleted it, it worked fine. Thanks!!!!!!!

 

[tarmand]

I want to do this again, but I need it to find the last column on worksheet "expenditures" with data and return the data in a specific row of that column (depending on where I insert the function).

For Instance, If I post this on the SMM worksheet in "A7", I will always need it to pull the data from row 94 of the expenditures sheet (from the last column with data)

Can anyone tell me what I need to do?