Cell Reference to a location

[Barney]

In a macro or formula, how do I make a reference to a cell by location?
i.e. 3 cells to the left and up one row?

Thanks,

Barney

 

[Nick Hodge]

Barney

Activecell.Offset(-3,-1)

Be aware you may raise an error if this reference doesn't exist



--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Gord Dibben]

Barney

Use the OFFSET worksheet function

=SUM(B2:OFFSET(E30,-1,-3))

Would sum up B2:B29...........B29 is OFFSET from E30 by one row up(29) and 3
columns left(B)

Macro.............

Sub Test()
Set qwerty = ActiveCell.Offset(-1, -3)
MsgBox qwerty.Address
End Sub

Gord Dibben Excel MVP

 

[Barney]

I have tried to use OFFSET in a macro to no avail. Here is what I am trying
to do. I have about 50 golfers. Their names are in Col A. Columns B-G are
set up with their last six scores (if they have that many). When any one
golfer plays his 7th round, I want to 'cut and paste' his last 5 scores to
replace the first 5 scores. This leaves a space to enter a new "6th" score.
With those scores I will calculate a handicap for each golfer.

I want to use a macro (with button) to activate the 'cut and paste'
operation. Not all golfers play each time so I want a button for each
golfer but only one macro in the spreadsheet so it doesn't get too large.

I am using Win XP and Excel 2002.

Thanks for your previous answer and any other help you can offer.

Barney