Charts Range Setting

[Nigel]

Hi All
I have a predefined chart that I want to set the range collection
dynamically from VBA code. That works OK.

A mixed bar-line chart for 12 months of the year on the X-Axis. My problem
stems from variable number of values for each bar/line.

I set the ranges as follows.....

With chSales
.SeriesCollection(1).Values = shSales.Range("B29:B" & BMax)
.SeriesCollection(2).Values = shSales.Range("E29:E" & EMax)
.SeriesCollection(3).Values = shSales.Range("J29:J" & BMax)
.SeriesCollection(4).Values = shSales.Range("K29:K" & EMax)
.SeriesCollection(1).XValues = shSales.Range("A29:A40")
End With

The BMax and EMax ranges are variable length from 1 to 12 values. The X
values are fixed at 12. The problem arises in that the chart displays ONLY
the X-values up to the B or E max vlaues whichever is grerater. I need it
to show all 12 months regardless of how many values for the chart series
there are, and DO not want the lines in the chart to drop to zero for the
remainder of the chart.

It must be possible?

--
Cheers
Nigel

[Tushar Mehta]

Plot 29:40 for each column of interest and do either of the following:

(1) Make sure the cells that do not contain data are truly empty (no
formula, no zero-length string (""), no nothing. In addition, ensure
that the Tools | Options... | Chart tab | 'Plot empty cells as' option
is set to 'Not plotted'.

(2) Replace the cells that should be empty with NA().

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <uurD$JkTFHA.2568@TK2MSFTNGP10.phx.gbl>, nigel-
sw@suxnospampanet.com says...
> Hi All
> I have a predefined chart that I want to set the range collection
> dynamically from VBA code. That works OK.
>
> A mixed bar-line chart for 12 months of the year on the X-Axis. My problem
> stems from variable number of values for each bar/line.
>
> I set the ranges as follows.....
>
> With chSales
> .SeriesCollection(1).Values = shSales.Range("B29:B" & BMax)
> .SeriesCollection(2).Values = shSales.Range("E29:E" & EMax)
> .SeriesCollection(3).Values = shSales.Range("J29:J" & BMax)
> .SeriesCollection(4).Values = shSales.Range("K29:K" & EMax)
> .SeriesCollection(1).XValues = shSales.Range("A29:A40")
> End With
>
> The BMax and EMax ranges are variable length from 1 to 12 values. The X
> values are fixed at 12. The problem arises in that the chart displays ONLY
> the X-values up to the B or E max vlaues whichever is grerater. I need it
> to show all 12 months regardless of how many values for the chart series
> there are, and DO not want the lines in the chart to drop to zero for the
> remainder of the chart.
>
> It must be possible?
>
>

[Nigel]

Hi Tushar
Thanks for the advice.

In my range I have a conditional formula that looks like this
=IF(D20=0,"",D20*5) which results in the cell showing blank (empty(?)) but a
point shows on the chart .

If I change the formula to =IF(D20=0,NA(),D20*5) I end up with a nasty
#N/A in the cell which detracts from the table looks, but it does not show
the point on the chart.

Is there a way to create a blank cell AND not show the point if the cell has
a formula?

--
Cheers
Nigel



"Tushar Mehta" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
news:MPG.1cdfda123199e3e398afc1@msnews.microsoft.com...
> Plot 29:40 for each column of interest and do either of the following:
>
> (1) Make sure the cells that do not contain data are truly empty (no
> formula, no zero-length string (""), no nothing. In addition, ensure
> that the Tools | Options... | Chart tab | 'Plot empty cells as' option
> is set to 'Not plotted'.
>
> (2) Replace the cells that should be empty with NA().
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <uurD$JkTFHA.2568@TK2MSFTNGP10.phx.gbl>, nigel-
> sw@suxnospampanet.com says...
> > Hi All
> > I have a predefined chart that I want to set the range collection
> > dynamically from VBA code. That works OK.
> >
> > A mixed bar-line chart for 12 months of the year on the X-Axis. My
problem
> > stems from variable number of values for each bar/line.
> >
> > I set the ranges as follows.....
> >
> > With chSales
> > .SeriesCollection(1).Values = shSales.Range("B29:B" & BMax)
> > .SeriesCollection(2).Values = shSales.Range("E29:E" & EMax)
> > .SeriesCollection(3).Values = shSales.Range("J29:J" & BMax)
> > .SeriesCollection(4).Values = shSales.Range("K29:K" & EMax)
> > .SeriesCollection(1).XValues = shSales.Range("A29:A40")
> > End With
> >
> > The BMax and EMax ranges are variable length from 1 to 12 values. The X
> > values are fixed at 12. The problem arises in that the chart displays
ONLY
> > the X-values up to the B or E max vlaues whichever is grerater. I need
it
> > to show all 12 months regardless of how many values for the chart series
> > there are, and DO not want the lines in the chart to drop to zero for
the
> > remainder of the chart.
> >
> > It must be possible?
> >
> >

[Tushar Mehta]

I knew the question about aesthetics was coming.

Just hide the #N/A through conditional formatting. See
http://groups-
beta.google.com/group/microsoft.public.excel.charting/browse_thread/thr
ead/e8029a50a033b833/3b17d65c02268e63?q=conditional+na+group:
*Excel*+author:tushar&rnum=1&hl=en#3b17d65c02268e63

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <OP09U1wTFHA.612@TK2MSFTNGP12.phx.gbl>, nigel-
sw@suxnospampanet.com says...
> Hi Tushar
> Thanks for the advice.
>
> In my range I have a conditional formula that looks like this
> =IF(D20=0,"",D20*5) which results in the cell showing blank (empty(?)) but a
> point shows on the chart .
>
> If I change the formula to =IF(D20=0,NA(),D20*5) I end up with a nasty
> #N/A in the cell which detracts from the table looks, but it does not show
> the point on the chart.
>
> Is there a way to create a blank cell AND not show the point if the cell has
> a formula?
>
>

[Nigel]

You guessed it right!

I had be playing around with conditinal formatting - your tip makes it work.
Pity I need to set each cell in the range one by one?

Thanks again

--
Cheers
Nigel



"Tushar Mehta" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
news:MPG.1cdfe0a7ad49d8bd98afc5@msnews.microsoft.com...
> I knew the question about aesthetics was coming.
>
> Just hide the #N/A through conditional formatting. See
> http://groups-
> beta.google.com/group/microsoft.public.excel.charting/browse_thread/thr
> ead/e8029a50a033b833/3b17d65c02268e63?q=conditional+na+group:
> *Excel*+author:tushar&rnum=1&hl=en#3b17d65c02268e63
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <OP09U1wTFHA.612@TK2MSFTNGP12.phx.gbl>, nigel-
> sw@suxnospampanet.com says...
> > Hi Tushar
> > Thanks for the advice.
> >
> > In my range I have a conditional formula that looks like this
> > =IF(D20=0,"",D20*5) which results in the cell showing blank (empty(?))
but a
> > point shows on the chart .
> >
> > If I change the formula to =IF(D20=0,NA(),D20*5) I end up with a nasty
> > #N/A in the cell which detracts from the table looks, but it does not
show
> > the point on the chart.
> >
> > Is there a way to create a blank cell AND not show the point if the cell
has
> > a formula?
> >
> >

[Tushar Mehta]

No, you don't have to do one cell at a time. Select all cells and set
the conditional formatting formula to use a relative address as in
=ISNA(A1) rather than $A$1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <u#6eKGxTFHA.616@TK2MSFTNGP12.phx.gbl>, nigel-
sw@suxnospampanet.com says...
> You guessed it right!
>
> I had be playing around with conditinal formatting - your tip makes it work.
> Pity I need to set each cell in the range one by one?
>
> Thanks again
>
>

[Nigel]

Brilliant!!

--
Cheers
Nigel



"Tushar Mehta" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
news:MPG.1cdfe75bc7781ced98afc8@msnews.microsoft.com...
> No, you don't have to do one cell at a time. Select all cells and set
> the conditional formatting formula to use a relative address as in
> =ISNA(A1) rather than $A$1.
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <u#6eKGxTFHA.616@TK2MSFTNGP12.phx.gbl>, nigel-
> sw@suxnospampanet.com says...
> > You guessed it right!
> >
> > I had be playing around with conditinal formatting - your tip makes it
work.
> > Pity I need to set each cell in the range one by one?
> >
> > Thanks again
> >
> >