• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Two questions about charts within VBAs

O

OleMiss2010

Member
Each time I run a macro I have created I need to delete the four charts in cells (A1:A4) on a "Charts" tab and create new charts to be placed in those cells. The code below is a sample of one of the charts I need to create.

[pre]
Code: 
Sheets("Charts").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Stats!$B$1:$" & Cells(1, n + 1)
ActiveChart.SeriesCollection(1).Values = "=Stats!$B$5:$" & Cells(5, n + 1)
[/pre]
The issue I'm having is in selecting the range for my X and Y values. The n value is an integer that will determine the appropriate size of the chart range and will be variable each time the macro runs. Earlier in the macro n is defined, so all I need to do is correctly incorporate it into the selection of my range. Any help would be greatly appreciated.
 
I had originally set the formula to cover the maximum area for the data and them the charts would automatically adjust based on the values or blanks as the case may be; the problem came when I updated the data and it didn't take up all of the cells being referenced. My scatter plots didn't return accurate representations of the data. The only workaround I could come up with was to recreate the charts to fit the data perfectly each time the macro runs.
 
Sounds like a perfect case for Named Formula

Can you post some data as an example?
 
Unfortunately no. Essentially what I have thought is 4 charts, each looking at a random number of data points (depending on the size of the simulation being run) and their associated normal distribution. The four charts always reference data in the same rows, just different numbers of columns each time. The number of columns should equal a defined integer "n" + 1 (since the data starts in column B).


Does this suffice?
 
Can you post a set of data to show what you require ?

Or just a sample of the data areas/rows you mention

I understand the column ranges will change

You can randomise the data and change the names to A, B, C etc, it is the layout that is important, not the data


You can email me directly if needed
 
Run the VBA below and you'll have a pretty good picture of what I'm dealing with. I only put data in 10 cells on the data tab but I left the formula references the same as what I currently have (1000 cells).

Sub Macro1()
'
' Macro1 Macro
'

'
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Stats"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Charts"
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
Columns("A:A").Select
Selection.ColumnWidth = 82
Cells.Select
Selection.RowHeight = 221
Range("A1").Select
Sheets("Stats").Select
ActiveCell.FormulaR1C1 = "A"
Range("A2").Select
ActiveCell.FormulaR1C1 = "B"
Range("A3").Select
ActiveCell.FormulaR1C1 = "C"
Range("A4").Select
ActiveCell.FormulaR1C1 = "D"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Norm - A"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Norm - B"
Range("A7").Select
ActiveCell.FormulaR1C1 = "Norm - C"
Range("A8").Select
ActiveCell.FormulaR1C1 = "Norm - D"
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3"
Range("B4").Select
ActiveCell.FormulaR1C1 = "4"
Range("C1").Select
ActiveCell.FormulaR1C1 = "2"
Range("C2").Select
ActiveCell.FormulaR1C1 = "4"
Range("C3").Select
ActiveCell.FormulaR1C1 = "5"
Range("C4").Select
ActiveCell.FormulaR1C1 = "6"
Range("B1:C4").Select
Selection.AutoFill Destination:=Range("B1:K4"), Type:=xlFillDefault
Range("B1:K4").Select
Range("B5").Select
ActiveCell.FormulaR1C1 = _
"=NORMDIST(R[-4]C,AVERAGE(R[-4]C2:R[-4]C1001),STDEV(R[-4]C2:R[-4]C1001),)"
Range("B5").Select
Selection.Copy
Range("B5:B8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("B5:K8").Select
ActiveSheet.Paste
Range("H3").Select
Sheets("Charts").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='Stats'!$B$1:$ALM$1"
ActiveChart.SeriesCollection(1).Values = "='Stats'!$B$5:$ALM$5"
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 1").Activate
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
Range("A2").Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
Range("A2").Select
ActiveSheet.Paste/>Range("A3").Select
ActiveSheet.Paste
Range("A4").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-75
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "='Stats'!$B$2:$ALM$2"
ActiveChart.SeriesCollection(1).Values = "='Stats'!$B$6:$ALM$6"
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "='Stats'!$B$3:$ALM$3"
ActiveChart.SeriesCollection(1).Values = "='Stats'!$B$7:$ALM$7"
ActiveWindow.SmallScroll Down:=3
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.SeriesCollection(1).XValues = "='Stats'!$B$4:$ALM$4"
ActiveChart.SeriesCollection(1).Values = "='Stats'!$B$8:$ALM$8"
Sheets("Stats").Select
ActiveWindow.SmallScroll Down:=-15
Range("A1").Select
End Sub
 
OleMiss


I have uploaded a file based on your data

https://rapidshare.com/files/1518810713/NormDist.xlsx


I have configured the 4 charts to calc the data without using the Norm Dist rows 5-8

Which I deleted


Have a look at the Named Formulas

n: the number of data points

Data_A: The Data for Data Set A

NormDist_A: The Normal Distribution for Data A


The chart is then based on

X_Series: Data_A

Y_Series: NormDist_A


etc


Add data and then change the n value


You could link n to the number of entries in say Row 2

n: =Counta($2:$2)-1


Note there is no VBA Code now at all


Is this what you were originally after?
 
You must log in or register to reply here.
Back
Top