In a calendar function, I have cells with the date of each day in the month. Two cells below are cells which vlookup data for that day.
I need to look at the cell containing the day's date, and compare it to a named cell containing today's date. If the cell with the day's date
is <= to "Today" then I want to change the formula in two cells to constants (f2 and f9 function). When this is done, move to the next date,
and if that date is today, exit the macro without further changes.
example: TODAY is a named range which is cell c2 in tab "year" in the workbook (Its value is MONTH(NOW())&"/"&DAY(NOW())&"/"&YEAR(NOW())now())
in order to strip off the time from the date value. If there is something more elegant than this, I am all ears...
b14 - d14 - f14 - h14 - j14 - l14 - n14
b20 - d20 - f20 - h20 - j20 - l20 - n20
b26 - d26 - f26 - h26 - j26 - l26 - n26
b32 - d32 - f32 - h32 - j32 - l32 - n32 <-- These are the cells with the individual day
b38 - d38 - f38 - h38 - j38 - l38 - n38
b44 - d44 - f44
The cells with the formulae are two cells below each day cell
example: b16:b17 - d16:d17 and so forth
<-- THese are the cells with the vlookups which need
b16 is merged with c16 <-- to be converted to constants rather than formulas
c17 is merged with c17 and so forth <-- using the f2, f9 operations
I'm used to QUATTRO's macro recorders which recorded keystrokes, VB is a whole new world
In the keystroke world the concept would be:
MACRO:
Select b14
If b14 > TODAY then END ELSE select b16:press F2 : press F9 : select b17: press F2: press F9
Select D14
If d14 > TODAY then END ELSE select d16:press F2 : press F9 : select d17: press F2: press F9
I guess I could make an array of the day cells, and read them off one by one and perform a subroutine rather than 38 steps.
The calendar is shown here if this will help you visualize it:
http://drdumont.webs.com/example.htm
I've uploaded a small part of the file (example.xls) which may help.
Thanks for taking the time to read this, and I'd really appreciate any pointers you may give
Yours for Better Television,
-- Doc
Try this...
Sub Update() Dim x As Byte Dim y As Byte For x = 14 To 30 Step 6 For y = 2 To 14 Step 2 If Not IsEmpty(Cells(x, y)) And Day(Cells(x, y)) < Day(Date) Then Cells(x + 2, y) = Cells(x + 2, y).Value Cells(x + 3, y) = Cells(x + 3, y).Value End If Next y Next x End Sub
Try using =TODAY()MONTH(NOW())&"/"&DAY(NOW())&"/"&YEAR(NOW())now())
in order to strip off the time from the date value. If there is something more elegant than this, I am all ears...
Last edited by dangelor; 02-10-2012 at 01:49 PM. Reason: Changed date check
Wow! That is SOME tight coding!
Duhh! forgot about TODAY function.
Can't wait to get home and try your solution.
I really appreciate your time and your reply. Many thanks!
-- Doc
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks