L
Luch
Excel 2000 on Windows 2000
I'm looking for functionality similar to "ActiveCell" in VBA. I have
week-ending dates in Column A. Columns B through H are the days of the
week, Mon - Fri, so cell B1 = "Mon", C1 = "Tue", etc. I want B2 to be
whatever the date value in Column A in the current row is, minus 6 (my
weeks end on Sunday.) So if my active cell is B6; A6 = 1/19/03; and
then I want B2 to read 1/13/03 (Monday's date), or 1/19/03 - 6.
A B C D E F G H
-------------------------------------------------------------
1 |Day--> Mon Tue Wed Thu Fri Sat Sun
2 |Date--> WE-6 WE-5 WE-4 WE-3 WE-2 WE-1 WE
3 |W/E
4 |5-Jan
5 |12-Jan
6 |19-Jan
7 |26-Jan
8 |2-Feb
Finding no ActiveCell function, I tried a user-defined function:
Public Function OffSetRef()
OffSetRef = Range("A" & ActiveCell.Row).Value
End Function
but this function in cell B2 returns #NAME?.
If this works, then I would extend the remaining days of the week:
cell C2 = OffSetRef - 5
cell D2 = OffSetRef - 4
cell E2 = OffSetRef - 3
cell F2 = OffSetRef - 2
etc.
What this would do is show the dates of the week in B2:H2 for each
week ending value of whatever current row I am on; it would change
each time I moved to a different row.
Plugging in the value <Range("A" & ActiveCell.Row).Value> from the
function above in the Immediate window in VBA Editor returns the
correct "A" column value. But for some reason, Excel does not
recognize the function on the sheet. Any ideas? thanks for any help.
I'm looking for functionality similar to "ActiveCell" in VBA. I have
week-ending dates in Column A. Columns B through H are the days of the
week, Mon - Fri, so cell B1 = "Mon", C1 = "Tue", etc. I want B2 to be
whatever the date value in Column A in the current row is, minus 6 (my
weeks end on Sunday.) So if my active cell is B6; A6 = 1/19/03; and
then I want B2 to read 1/13/03 (Monday's date), or 1/19/03 - 6.
A B C D E F G H
-------------------------------------------------------------
1 |Day--> Mon Tue Wed Thu Fri Sat Sun
2 |Date--> WE-6 WE-5 WE-4 WE-3 WE-2 WE-1 WE
3 |W/E
4 |5-Jan
5 |12-Jan
6 |19-Jan
7 |26-Jan
8 |2-Feb
Finding no ActiveCell function, I tried a user-defined function:
Public Function OffSetRef()
OffSetRef = Range("A" & ActiveCell.Row).Value
End Function
but this function in cell B2 returns #NAME?.
If this works, then I would extend the remaining days of the week:
cell C2 = OffSetRef - 5
cell D2 = OffSetRef - 4
cell E2 = OffSetRef - 3
cell F2 = OffSetRef - 2
etc.
What this would do is show the dates of the week in B2:H2 for each
week ending value of whatever current row I am on; it would change
each time I moved to a different row.
Plugging in the value <Range("A" & ActiveCell.Row).Value> from the
function above in the Immediate window in VBA Editor returns the
correct "A" column value. But for some reason, Excel does not
recognize the function on the sheet. Any ideas? thanks for any help.