Relative references in macros for graphs

[Bert Kirsten]

I am trying (Excel 2003 Window XP home) to create a number of graphs (±200)
using a macro. The sequence I use is as follows:

Select cell at top left of data range
Go to "tools" "macros" "create new macro"
Select a hotkey
hit "OK'
check 'relative reference' button
select it (click it a few time so it shows selected)
Drag cursor to select the range
click on 'chart wizard' and go through chart creation routine through to
'finish'
click on 'stop recording'

For the next set of data I place the cusor on the top left of the next
range and hit the hot key . Problem is it just keeps on going back to the
original range.

I have tried all combinatons I can think off selecting relative referencing
'on' or 'off' , at the start or end of recording and it just has no effect.

Other people have had this problem. The dialogue below is one response that
seems to have helped but, not being adept at programming I find it a bit
daunting...

Is there any other way ?

===================================================

Hmm... it seems relative referencing doesn't work with chart objects. The

 

[CyberTaz]

You've posted to the Excel for Mac newsgroup & I'm not a terrific coder, but
the one problem I see is that you are including the positioning of your cell
selector as the first action in your macro. If you select the range first
then record the other steps it may work as you prefer, but as long as you
record "select Sheet1!B10:F25" as a part of the macro's operation that's
what it will do every time. What you're doing now will only re-chart the
same range every time you run the macro.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Bert Kirsten]

thanks - but that don't work either - been there done that as it were....

I girded up the old loins and made the code changes suggested by someone
else and did get it right eventually - terribly proud of myself I am. But I
really think the softwatre shouldbe able to do it itself ...

 

[Bob Greenblatt]

thanks - but that don't work either - been there done that as it were....

I girded up the old loins and made the code changes suggested by someone
else and did get it right eventually - terribly proud of myself I am. But I
really think the softwatre shouldbe able to do it itself ...

PMFJI, but the software is certainly capable of doing it itself. You just
recorded the wrong steps. Bob (Cybertaz) gave good advice about selecting
the area to chart first. That should have worked as you intended. In any
case, I'm pleased you were able to modify the code and got things working.