Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Reference to a location
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Reference to a location
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 HIS "Barney" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Reference to a location
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 On Mon, 13 Dec 2004 17:43:51 -0500, "Barney" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Reference to a location
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 "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 On Mon, 13 Dec 2004 17:43:51 -0500, "Barney" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference Worksheet Location from web | Links and Linking in Excel | |||
Cell Location Reference | Excel Discussion (Misc queries) | |||
Creating a linked file based on variable location reference | Excel Discussion (Misc queries) | |||
Cell Reference Location | Excel Discussion (Misc queries) | |||
Reference cell location | Excel Worksheet Functions |