vba loses a range on data (excelxp)

Topic

New Reply

Hi,

I hope one of you can help me out this situation.
In vba I want to automate a graph but although when recording the macro i have the desired data range chosen it seems to get lost when executing the macro.
I get only 4 data ranges instead of 5. The first one is always dropped.
Guidance and help are welcome.

Reply | Quote

Replies

There are several macros for creating a chart in your workbook (plus other macros whose purpose is not clear to me). Which one should we look at?
Is the chart on the sheet “grafblad Virgin Refter” an example of the result of your macro, or an example of what you want to accomplish?
Please provide sufficient information for us to help you.

Reply | Quote

hello hans
,module4 the grafiek subroutine I would like to use.
The grafblad Virgin Refter is indeed a partially result. (Had to dispose a lot to get it into a 100k-zip file).
You can see in the coffee graph ,the last graph on the page, the first range hasn’t been included.
Look for the “deca” figures.

When viewing the properties of that particular graph the information isn’t the same anymore like in the first one.
The ranges are gone and i just copied the code from the first graph and adjusted it to the situation.

Thanks for taking it up but do not forget to sleep. Another working day is in the making.

Bye,

Reply | Quote

When the coffee & soup chart is created from the range D90:H101, D90:D101 is interpreted as category labels, while columns E through H provide the values for 4 series. It’s better to specify each of the series explicitly:

Dim i As Integer
For i = 4 To 8
ActiveChart.SeriesCollection.Add Sheets(actsheet).Range(Sheets(actsheet).Cells(90, i), _
Sheets(actsheet).Cells(101, i)), xlColumns, False, False, False
Next i
ActiveChart.SeriesCollection(1).XValues = Sheets(actsheet).Range(“A90:A101”)

Reply | Quote

Hans,

this works great and the code is shorter an perhaps a bit faster.

In normal language it could be read I think : for the active sheet add data to be found at sheet named actsheet from the range on actsheet starting from cell in row90 up to cell up to row101. to be repeated 5 times (i) by which we take (i) each time as the row number (cfr cells(rowindex,columnindex) plot by columns
One question what stands the 3 falses for?

you made my day and labour worthwhile

Reply | Quote

Does this from the Help file tell you what you wanted to know”

[indent]

---

Add Method (SeriesCollection Collection)

Adds one or more new series to the SeriesCollection collection.

Syntax

expression.Add(Source, Rowcol, SeriesLabels, CategoryLabels, Replace)

expression Required. An expression that returns a SeriesCollection object.

Source Required Variant. The new data, either as a Range object or an array of data points.

Rowcol Optional Variant. Specifies whether the new values are in the rows or columns of the specified range. Can be one of the following XlRowCol constants: xlRows or xlColumns. The default value is xlColumns.

SeriesLabels Optional Variant. Ignored if Source is an array. True if the first row or column contains the name of the data series. False if the first row or column contains the first data point of the series. If this argument is omitted, Microsoft Excel attempts to determine the location of the series name from the contents of the first row or column.

CategoryLabels Optional Variant. Ignored if Source is an array. True if the first row or column contains the name of the category labels. False if the first row or column contains the first data point of the series. If this argument is omitted, Microsoft Excel attempts to determine the location of the category label from the contents of the first row or column.

Replace Optional Variant. If CategoryLabels is True and Replace is True, the specified categories replace the categories that currently exist for the series. If Replace is False, the existing categories will not be replaced. The default value is False.

Remarks

This method is not available for PivotChart reports.

---

[/indent]

Reply | Quote