> Would you please give me a clue?
Clue only, as I don't have anything to test it on.
Inside your loop, you are finding cells that you wish to put into the chart.
Pairs of cells, actually ... one each for the axis ant the values.
When you have a cell that needs to go to the chart, add its address to a
comma-separated string of similar cells. E.g. instead of
> Set FacVal = ActiveCell
> ActiveCell.Offset(1, -4).Range("A1").Select
> Set FacName = ActiveCell
something like
> yRangeString = yRangeString & "," & ActiveCell.Address
> ActiveCell.Offset(1, -4).Range("A1").Select
> xRangeString = xRangeString & "," & ActiveCell.Address
so you end up with a comma, and then a list of cell addresses, separated by
commas, in each one.
Then you can convert them into ranges with something like
> Set yRange = Range(Mid(yRangeString,2,999))
> Set xRange = Range(Mid(xRangeString,2,999))
and pass them to the new series call.
Note:
1) I'm just typing this in ... it's completely untested
2) You'll need to find something else to give to the new series as its name
3) I don't know whether you can use these types of "broken" ranges in
charts at all
===================
What actually happens if you create a series covering all the cells,
including the empty ones? I'd have hoped that chart would simply ignore the
empty cells, but it mightn't.
Regards, Dave S
----- Original Message -----
From: "jasarmiento" <jasarmiento@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Wednesday, February 22, 2012 10:44 AM
Subject: [ExcelVBA] Re: Problem with charts
> HI Dave,
>
> Thanks for your answer. You're right I was testing and I have to create a
> "new series", but my problem is that I don't know how to create this
> series as string with non consecutive ranges. Would you please give me a
> clue?
>
> Thanks again,
>
> Julio
>
> --- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@...>
> wrote:
>>
>> You appear to be creating new series from within the loop. I suspect
>> that
>> this creates lots of new series. You presumably want to create only one
>> new
>> series - outside the loop.
>>
>> If you want to give the chart a pair of disjoint ranges (and I'm not even
>> sure you can do that), then you'll need to create the ranges as you go,
>> inside the loop, then pass them to a single "new series" call. You might
>> find it easier to keep the ranges as text strings while you build them
>> up.
>>
>> Regards, Dave S
>>
>> ----- Original Message -----
>> From: "jasarmiento" <jasarmiento@...>
>> To: <ExcelVBA@yahoogroups.com>
>> Sent: Wednesday, February 22, 2012 3:05 AM
>> Subject: [ExcelVBA] Problem with charts
>>
>>
>> > Hi all,
>> >
>> > I'm trying to add series to a chart while there is any text info in
>> > Column
>> > A (titles) and there is any value in Column E.
>> > It is a simple barchart with some values in Y-Axis (column E) and the
>> > series titles as X-axis (column is A). I created a loop but the chart
>> > doesn't show all titles in X-axis. X-axis only shows the first name.
>> > Can
>> > anybody help me?
>> >
>> > I'm attaching the code:
>> >
>> > Sub AddNewSeries()
>> >
>> > Dim f1 As Integer
>> > Dim FacVal As Range
>> > Dim FacName As Range
>> >
>> > ActiveSheet.Shapes("faculty").Select
>> >
>> > Range("A20").Select
>> > Set FacName = ActiveCell
>> > Do While Len(FacName) > 1
>> > ActiveCell.Offset(0, 4).Range("A1").Select
>> > f1 = ActiveCell
>> > If f1 > 0 Then
>> > Set FacVal = ActiveCell
>> > ActiveSheet.Shapes("faculty").Select
>> > With ActiveChart.SeriesCollection.NewSeries
>> > .Name = FacName
>> > .XValues = FacName
>> > .Values = FacVal
>> > ActiveCell.Offset(1, -4).Range("A1").Select
>> > Set FacName = ActiveCell
>> > End With
>> > Else
>> > ActiveCell.Offset(1, -4).Range("A1").Select
>> > Set FacName = ActiveCell
>> > End If
>> > Loop
>> >
>> > End Sub
>> >
>> >
>> > Thank you.
>> >
>> >
>> >
>> > ------------------------------------
>> >
>> > ----------------------------------
>> > 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
>> >
>> > ----------------------------------Yahoo! Groups Links
>> >
>> >
>> >
>> >
>> >
>> > -----
>> > No virus found in this message.
>> > Checked by AVG - www.avg.com
>> > Version: 10.0.1424 / Virus Database: 2112/4821 - Release Date: 02/20/12
>> >
>>
>
>
>
> ------------------------------------
>
> ----------------------------------
> 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
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1424 / Virus Database: 2113/4824 - Release Date: 02/21/12
>
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
----------------------------------
Tidak ada komentar:
Posting Komentar