Excel relative reference not working with charts

  • Thread starter Thread starter CzarCW
  • Start date Start date
C

CzarCW

Hello all,

I'm very new to making macros, so I appreciate any and all help. I hav
two tables of data on the same worksheet (e.g. one table is the maximu
value, the other the minimum value). I want to create a "Clustere
Column" chart that shows the progression of the min and max values ove
a series of tests. Each test is its own column, and there are severa
rows that need to be plotted. I want the macro to take the currentl
highlighted cell, and use that row to make the charts. Here's a copy o
what I've made so far:

Sub Chart_maker()
'
' Chart_maker Macro
' This macro should create column charts based on the currentl
highlighted cell.
'

'
* ActiveCell.Offset(0, 4).Range("A1:AD1").Select
* Charts.Add
* ActiveChart.ChartType = xlColumnClustered
* ActiveChart.SetSourceDat
Source:=Sheets("Sheet1").Range("E3:AH3"), _
* PlotBy:=xlRows
* ActiveChart.SeriesCollection.NewSeries
* ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!R2C5:R2C34"
* ActiveChart.SeriesCollection(1).Name = "='Sheet1'!R1C5:R1C36"
* ActiveChart.SeriesCollection(2).Values = "='Sheet1'!R3C37:R3C66"
* ActiveChart.SeriesCollection(2).Name = "='Sheet1'!R1C37:R1C68"
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.Deselect
End Sub

The lines with asterisks are the ones where Excel is looking at
particular cell address, whereas I want the ones relative to where
started. Any help
 
a macro will not select high lighted rows.
macro work outside of excel and all the built in goodies.
sorry.
you will have to tell it via code which row to select.
 
Back
Top