Starting a macro execution where the cursor happens to be

[tayseer.abdelhalim]

I recorded a macro to chart a range of data. But since the data can
be
at any cell in the worksheet, I would like to generalize the macro by
telling it to start at the cell where the cursor happens to be.....I
would appreciate it if someone can tell me the code to do that

Tayseer

 

[Guest]

hi Tayseer

A relative dynamic named range is one possibility & another is resizing a
range based on the activecell in VBA - here are some brief instructions...

1)
*make a static embedded chart of your data (provides a canvas to work on)

*Select the header row of a column of data that you want to be the base from
where your dynamic chart will be built
*create a named range eg
"ChartAmount=OFFSET(report!$B1,1,RAND()*0,COUNTA(report!$B:$B)-1)"
(this is assuming column B is your target, that the Header row is in row 1 &
the series goes down the column)

*select the series on your chart for column B & change the formula bar from
something like "=SERIES(report!$b$1,,report!$b$2:$b$7,1)" to use the defined
name ie "=SERIES(report!$c$1,,Book1.xls!ChartAmount,3)"

The use of Rand() in the named range means it will adjust the range based on
the active cell each time the ss calculates. To help this become
automatically updating right click on the sheet tab - View code & paste in
the below macro:

option explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Exit if the Header row or a range is not selected
If TypeName(Target) <> "Range" Or Target.Row = 1 _
Or Target.Column <> 2 Then Exit Sub
Application.Calculate
End Sub


For more informed/complete suggesions check out any of the links on Jon
Peltier's website:
http://www.peltiertech.com/Excel/Charts/DynamicChartLinks.html

2)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim DynChartDataSource As Range
Set DynChartDataSource = ActiveCell.Resize(12, 3)
''your code to tie this new range into an existing chart
end sub

hth
Rob

__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...