Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Betekom, Belgium
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vba loses a range on data (excelxp)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vba loses a range on data (excelxp)

    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.

  3. #3
    Star Lounger
    Join Date
    Sep 2003
    Location
    Betekom, Belgium
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba loses a range on data (excelxp)

    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,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vba loses a range on data (excelxp)

    When the coffee & soup chart is created from the range D90:H101, D90101 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")

  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Location
    Betekom, Belgium
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba loses a range on data (excelxp)

    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

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba loses a range on data (excelxp)

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

    <hr>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.
    <font color=red>
    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.
    </font color=red>
    Remarks

    This method is not available for PivotChart reports.
    <hr>
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •