How to refer to active cell on spreadsheet?

[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.

 

[Tom Ogilvy]

in B2 put in
=$A2+(column()-8)

then drag fill over to column H (cell H2), then select B2:H2 and drag fill
down

the formula will automatically adjust to do what you want.

The $ fixes the column reference and the row reference adjusts accordingly.