Fast Solution to Problem: Obtaining a date in Excel using VBA/Function

Obtaining a date in Excel using VBA/Function

I have data laid out horizontally in Excel similar to this:
 
1          JAN1          FEB1          MAR1          APR1          May1
2          JAN31         FEB28       MAR31        APR30         May31
3   ??                                             8                8
4   ??                                                              16                 24
 
The data represents timephasing of manhours for given periods of time (as illustrated, months), the data are real dates such as 4/15/2011.  Row 1 are period starts, row2 are period finishes.  Rows three and greater contain mostly blank cells, but some of
these time periods will have manhours associated with them.  In this illustration I am attempting to show manhours in March and April with Jan & Feb being blank (Row3).  In row 4 I am attempting to show manhours in April and May.  We may assume that once manhours
are listed, they are contiguous so there will be no "holes" in the spread.  The spread could be many time periods.
I can guarantee the time periods are contiguous, but not necessarily all in months (some might be weeks, others months, back and forth, all on
the same two rows).
 
I need some type of function or VBA code to scan accross row3 until it finds the first non-blank cell and then runs that up to the top so I can determine the Start and Finish Dates of the column where hours first appear.  I need the function/formula to return
a date (in this case, it would return March 1st to replace the ??  on row 3
 
For those familiar with Microsoft Project, the intention here is essentially to duplicate the functionality of the TaskUsage or ResourceUsage view in Project.  In the best solution, the result will dynamically change if I were to add manhours to February,
etc.
 
The real solution, will involve me doing this twice (in two separate columns), once when I find the first date (March) and write that to ??, then once I find the last occupied cell (Here it is April) I would write April 30th to a difference cell, perhaps
far to the right of April.
 
Once Row3 is happy, I move to Row4

Keys to the Problem Obtaining a date in Excel using VBA/Function

Download Error Fixer (Free)

Your VBA routine is part of a macro, not a UDF, but it should work.  However, since all you are doing is writing the formula into some cells, there is no reason to "Select" the cells.  Also, you can fill down without going to each cell.  Finally, if you
are using R1C1 notation style, and doing the more efficient fill down instead of entering each line individually, you should use the relative row addressing style when referring to the ManHours rows.  So, try:
 
Range("A3").FormulaArray = _
 "=INDEX(R1C3:R1C11,1,MATCH(TRUE,ISNUMBER(RC3:RC11),0))"
Range("A3").NumberFormat = "m/d/yyyy"
Range("B3").FormulaR1C1 = "=LOOKUP(2,1/ISNUMBER(RC3:RC11),R2C3:R2C11)"
Range("B3").NumberFormat = "m/d/yyyy"
Range("A3:B100").FillDown
------------------------------
The "2" in the LOOKUP function does NOT refer to row 2.  It is a number guaranteed to be larger than any value in the array generated by the next term (the Lookup_Vector), which will be an array consisting of either the numeric value "1", or the error value
#DIV/0!  LOOKUP will thus return the last value in the array, which will be the last entry, and return the corresponding entry from Row 2 (the Result Vector

Open Action Center by clicking the Start button Picture of the Start button, clicking Control Panel, and then, under System and Security, clicking Review your computer's status.

Update and run your antivirus software. Windows doesn't come with antivirus software, but Action Center can often monitor the antivirus software that you or your computer manufacturer have installed.

Recommended Method to Fix the Problem: Obtaining a date in Excel using VBA/Function:

 

 

How to Fix Obtaining a date in Excel using VBA/Function with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your system.  Open it, and it will scan your system. The junk files will be shown in the list.

2. After the scan is done, you can see the errors and problems which need to be repaired.

3. The Fixing part is done, the speed of your computer will be much higher than before and the errors have been fixed.

Related: Babylon dictionary doesn t pop up when I use its activation hotkey on a text in Windows environment realtek drivers download Does a Windows 7 Installer Cleanup Utility exist realtek audio driver windows 7 I have multiple updates eleven that will not install on Windows 7 Pro This has been an issue for many months Please help realtek audio driver windows 7 Printing messages on Windows Mail realtek drivers download Sporadic Windows Explorer has stopped working even after hot fixes and repair install realtek driver Windows 7 won t load to login screen it keeps loading starting windows fixed realtek audio driver download Windows Server 2003 64bit Terminal Server has 2 mouse pointers when connecting via RDP realtek audio drivers three Scroll as a result of the record of hardware set up until you come across “Enhanced Display” listed.,Office 2103 - Word Works, Excel Won't Even Load.,Start Excel In Fullscreen Mode,Cannot Connect To Home Network After Connecting To Internet At Duke University,Error Code 1402 : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run\OptionalComponents\MSFS,Windows Update Install Error Code 8007371C
Read More: internet explorer toolbar,Unable to submit the page information in IE 6 [Solved],[Solved] sending word doc in windows live mail.,Fast Solution to Error: FRUSTRATED WITH MICROSOFT SUPPORT-HOW TO REGAIN MY PRIMARY MSN EMAIL ACCOUNT?,Troubleshooter of Error: my ' turns windows featuire on or off ' this box remains empty..,Screen flickers while browsing. __,Want to stop Word from asking me if I want to merge documents(Office),Qualcom Atheros,Windows 7 - Black Screen,IE 8 on windows 7 or 8? I need it to access business portals that do not support 9 or higher