EXCEL FEED: Re: [ExcelVBA] Excel chart duplication

Jumat, 26 September 2014

Re: [ExcelVBA] Excel chart duplication

Do you mean this line?

Range("S8:AA8,"S"&i&":AA"&i+5).Select

The " after the comma is incorrect and will have it looking for a variable S.

Rule of thumb, there must always be an even number of ". You have 5.

You might have meant

Range("S8:AA8,S"&i&":AA"&(i+5)).Select

Note that I've put parentheses round the i+5 to make it clear to both Excel and humans that the addition is to be done first.

Also, it is possible that you might need spaces around the & operators to avoid syntax error reports. I find I have to do this occasionally, but have never bothered to remember when.

Regards, Dave S

----- Original Message -----

From: paul.vermeulen@vulcantech.com.au [ExcelVBA]

To: ExcelVBA@yahoogroups.com

Sent: Friday, September 26, 2014 4:16 PM

Subject: [ExcelVBA] Excel chart duplication

Hi all. Excel 2010, I have to create multiple charts on a data set that spans about a year of history, one month per sheet, AND each sheet has ~60 lines of data. For ease of readability, the first chart has only six data series that contain a fixed x-axis, data as y-axis and a series name that uses CONCATENATE for a descriptive Legend.

No problem in creating the first chart, and easy to copy and paste the chart, and then by clicking on each series in the chart, moving the ranges for x-axis, y-axis, and name fields.

How do I automate this?
1) Recorded a macro during creation of first chart and modified to that below. I need help with my concatenate formula in the second part of code to state that the selcted data will be the x-axis in row 8, and then a variable data range in groups of six rows at a time.

Regards

Paul

Sub GenTenCharts()

'This code generates ten size distribution charts on the active sheet.
'It is assumed that the data still lives in columns T to AA.
'Column S is populated with sample number and contractor name.

    Dim i As Long

'Concatenate values to column S for ease of charting.
    For i = 9 To 75
        Range("S" & i).Value = Range("E" & i).Value & " " & Range("C" & i).Value
    Next i

'Generate the charts

    For i = 9 To 69 Step 6

        Range("S8:AA8,"S"&i&":AA"&i+5).Select
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlXYScatterLines
        ActiveChart.SetSourceData Source:=ActiveSheet.Range("$S$8:$AA$14")
        ActiveChart.Axes(xlCategory).Select
        ActiveChart.Axes(xlCategory).MinimumScale = 0.01
        ActiveChart.Axes(xlCategory).MaximumScale = 10
        ActiveChart.Axes(xlCategory).ScaleType = xlLogarithmic
        ActiveChart.Axes(xlCategory).CrossesAt = 0.01
        ActiveChart.Axes(xlValue).Select
        ActiveChart.Axes(xlValue).MaximumScale = 1.2
        ActiveChart.Axes(xlValue).MaximumScale = 1
        Selection.TickLabels.NumberFormat = "0%"
        ActiveChart.Axes(xlCategory).Select
        ActiveChart.Axes(xlCategory).HasMajorGridlines = True
        ActiveChart.Axes(xlCategory).HasMinorGridlines = True

    Next i

End Sub

No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1432 / Virus Database: 4015/7769 - Release Date: 09/25/14

__._,_.___

Posted by: "David Smart" <smartware.consulting@gmail.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group