Help!

Excel inconsistency

  
  
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions RSS
Next:  Checkbox in UserForm updating to Word - help need..  
Author Message
Robert Baer
External


Since: Apr 07, 2009
Posts: 9
PostPosted: Wed Apr 15, 2009 8:37 pm    Post subject: Excel inconsistency
Archived from groups: microsoft>public>excel (more info?)
After a bunch of fiddling, i got a macro to duplicate a chart below a
single one on a worksheet.
But there was a runtime error as noted near the end of the macro.
So i did a lot of more fiddling and was able to get a number of
copies, one after the other by calling them all "Chart 1".
Nasty.
Did other fiddling in attempts to change series ranges and title on
the fly.
Things got worse, so i tried to go back to the original macro as seen
below.
Even deleting the XLSTART folder and re-trying the macro did no good.
**HELP**

Sub Macro2()
' Macro2 Macro
' Macro recorded 4/15/2009 by Robert Baer
Range("A1").Select
SheetColumn = 1
ActiveSheet.ChartObjects("Chart 1").Activate
'*NOW FAILS-------------------^---NOW arg needs to be 1 not "Chart 1"
For ChartNum = 1 To 20
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
'*NOW FAILS------------------------------------^
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
'*NOTE redundant and useless duplication below*
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Points(67).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Windows("StripperWells.xls").Activate
SheetColumn = SheetColumn + 21
TextSheetColumn = "A" + LTrim(Str$(SheetColumn))
ChartNum = ChartNum + 1
TextChartNum = "Chart" + Str$(ChartNum)
Range(TextSheetColumn).Select
ActiveSheet.Paste
ActiveSheet.ChartObjects(TextChartNum).Activate
' ** Runtime error 1004 - unable to get the ChartObjects property
' of the Worksheet class
' Note: second chart has been created where i wanted it, and
' it is selected.
'*That is to say, when it worked..i did a lot of mods and
' (as above) it is all messed up
Next ChartNum
End Sub
Back to top
Bob Umlas
External


Since: Dec 08, 2004
Posts: 51
PostPosted: Thu Apr 16, 2009 1:08 pm    Post subject: Re: Excel inconsistency [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Quick glance shows you should probably replace:
TextChartNum = "Chart" + Str$(ChartNum)
with
TextChartNum = "Chart " + Str$(ChartNum)
(note the space after "Chart")


"Robert Baer" <robertbaer DeleteThis @localnet.com> wrote in message
news:irKdnQKHmsJ2OnvUnZ2dnUVZ_vOdnZ2d@posted.localnet...
> After a bunch of fiddling, i got a macro to duplicate a chart below a
> single one on a worksheet.
> But there was a runtime error as noted near the end of the macro.
> So i did a lot of more fiddling and was able to get a number of copies,
> one after the other by calling them all "Chart 1".
> Nasty.
> Did other fiddling in attempts to change series ranges and title on the
> fly.
> Things got worse, so i tried to go back to the original macro as seen
> below.
> Even deleting the XLSTART folder and re-trying the macro did no good.
> **HELP**
>
> Sub Macro2()
> ' Macro2 Macro
> ' Macro recorded 4/15/2009 by Robert Baer
> Range("A1").Select
> SheetColumn = 1
> ActiveSheet.ChartObjects("Chart 1").Activate
> '*NOW FAILS-------------------^---NOW arg needs to be 1 not "Chart 1"
> For ChartNum = 1 To 20
> ActiveChart.ChartArea.Select
> ActiveChart.ChartArea.Copy
> ActiveChart.SeriesCollection(1).Select
> ActiveChart.SeriesCollection(2).Select
> ActiveChart.Axes(xlCategory).MajorGridlines.Select
> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
> '*NOW FAILS------------------------------------^
> ActiveChart.Axes(xlValue, xlSecondary).Select
> ActiveChart.PlotArea.Select
> ActiveChart.ChartArea.Select
> '*NOTE redundant and useless duplication below*
> ActiveChart.SeriesCollection(1).Select
> ActiveChart.SeriesCollection(2).Select
> ActiveChart.Axes(xlCategory).MajorGridlines.Select
> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
> ActiveChart.Axes(xlValue, xlSecondary).Select
> ActiveChart.PlotArea.Select
> ActiveChart.ChartArea.Select
> ActiveChart.SeriesCollection(1).Select
> ActiveChart.SeriesCollection(2).Select
> ActiveChart.Axes(xlCategory).MajorGridlines.Select
> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
> ActiveChart.Axes(xlValue, xlSecondary).Select
> ActiveChart.PlotArea.Select
> ActiveChart.ChartArea.Select
> ActiveChart.SeriesCollection(1).Select
> ActiveChart.SeriesCollection(2).Select
> ActiveChart.Axes(xlCategory).MajorGridlines.Select
> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
> ActiveChart.Axes(xlValue, xlSecondary).Select
> ActiveChart.PlotArea.Select
> ActiveChart.ChartArea.Select
> ActiveChart.SeriesCollection(1).Select
> ActiveChart.SeriesCollection(2).Select
> ActiveChart.Axes(xlCategory).MajorGridlines.Select
> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
> ActiveChart.Axes(xlValue, xlSecondary).Select
> ActiveChart.PlotArea.Select
> ActiveChart.ChartArea.Select
> ActiveChart.SeriesCollection(1).Select
> ActiveChart.SeriesCollection(2).Select
> ActiveChart.Axes(xlCategory).MajorGridlines.Select
> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
> ActiveChart.Axes(xlValue, xlSecondary).Select
> ActiveChart.PlotArea.Select
> ActiveChart.ChartArea.Select
> ActiveChart.SeriesCollection(1).Select
> ActiveChart.SeriesCollection(2).Points(67).Select
> ActiveChart.Axes(xlCategory).MajorGridlines.Select
> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
> ActiveChart.Axes(xlValue, xlSecondary).Select
> ActiveChart.PlotArea.Select
> ActiveChart.ChartArea.Select
> ActiveWindow.Visible = False
> Windows("StripperWells.xls").Activate
> SheetColumn = SheetColumn + 21
> TextSheetColumn = "A" + LTrim(Str$(SheetColumn))
> ChartNum = ChartNum + 1
> TextChartNum = "Chart" + Str$(ChartNum)
> Range(TextSheetColumn).Select
> ActiveSheet.Paste
> ActiveSheet.ChartObjects(TextChartNum).Activate
> ' ** Runtime error 1004 - unable to get the ChartObjects property
> ' of the Worksheet class
> ' Note: second chart has been created where i wanted it, and
> ' it is selected.
> '*That is to say, when it worked..i did a lot of mods and
> ' (as above) it is all messed up
> Next ChartNum
> End Sub
Back to top
Robert Baer
External


Since: Apr 07, 2009
Posts: 9
PostPosted: Fri Apr 17, 2009 2:08 am    Post subject: Re: Excel inconsistency [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Bob Umlas wrote:

> Quick glance shows you should probably replace:
> TextChartNum = "Chart" + Str$(ChartNum)
> with
> TextChartNum = "Chart " + Str$(ChartNum)
> (note the space after "Chart")
>
>
> "Robert Baer" <robertbaer DeleteThis @localnet.com> wrote in message
> news:irKdnQKHmsJ2OnvUnZ2dnUVZ_vOdnZ2d@posted.localnet...
>
>> After a bunch of fiddling, i got a macro to duplicate a chart below a
>>single one on a worksheet.
>> But there was a runtime error as noted near the end of the macro.
>> So i did a lot of more fiddling and was able to get a number of copies,
>>one after the other by calling them all "Chart 1".
>> Nasty.
>> Did other fiddling in attempts to change series ranges and title on the
>>fly.
>> Things got worse, so i tried to go back to the original macro as seen
>>below.
>> Even deleting the XLSTART folder and re-trying the macro did no good.
>>**HELP**
>>
>>Sub Macro2()
>>' Macro2 Macro
>>' Macro recorded 4/15/2009 by Robert Baer
>> Range("A1").Select
>> SheetColumn = 1
>> ActiveSheet.ChartObjects("Chart 1").Activate
>>'*NOW FAILS-------------------^---NOW arg needs to be 1 not "Chart 1"
>> For ChartNum = 1 To 20
>> ActiveChart.ChartArea.Select
>> ActiveChart.ChartArea.Copy
>> ActiveChart.SeriesCollection(1).Select
>> ActiveChart.SeriesCollection(2).Select
>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>>'*NOW FAILS------------------------------------^
>> ActiveChart.Axes(xlValue, xlSecondary).Select
>> ActiveChart.PlotArea.Select
>> ActiveChart.ChartArea.Select
>>'*NOTE redundant and useless duplication below*
>> ActiveChart.SeriesCollection(1).Select
>> ActiveChart.SeriesCollection(2).Select
>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>> ActiveChart.Axes(xlValue, xlSecondary).Select
>> ActiveChart.PlotArea.Select
>> ActiveChart.ChartArea.Select
>> ActiveChart.SeriesCollection(1).Select
>> ActiveChart.SeriesCollection(2).Select
>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>> ActiveChart.Axes(xlValue, xlSecondary).Select
>> ActiveChart.PlotArea.Select
>> ActiveChart.ChartArea.Select
>> ActiveChart.SeriesCollection(1).Select
>> ActiveChart.SeriesCollection(2).Select
>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>> ActiveChart.Axes(xlValue, xlSecondary).Select
>> ActiveChart.PlotArea.Select
>> ActiveChart.ChartArea.Select
>> ActiveChart.SeriesCollection(1).Select
>> ActiveChart.SeriesCollection(2).Select
>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>> ActiveChart.Axes(xlValue, xlSecondary).Select
>> ActiveChart.PlotArea.Select
>> ActiveChart.ChartArea.Select
>> ActiveChart.SeriesCollection(1).Select
>> ActiveChart.SeriesCollection(2).Select
>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>> ActiveChart.Axes(xlValue, xlSecondary).Select
>> ActiveChart.PlotArea.Select
>> ActiveChart.ChartArea.Select
>> ActiveChart.SeriesCollection(1).Select
>> ActiveChart.SeriesCollection(2).Points(67).Select
>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>> ActiveChart.Axes(xlValue, xlSecondary).Select
>> ActiveChart.PlotArea.Select
>> ActiveChart.ChartArea.Select
>> ActiveWindow.Visible = False
>> Windows("StripperWells.xls").Activate
>> SheetColumn = SheetColumn + 21
>> TextSheetColumn = "A" + LTrim(Str$(SheetColumn))
>> ChartNum = ChartNum + 1
>> TextChartNum = "Chart" + Str$(ChartNum)
>> Range(TextSheetColumn).Select
>> ActiveSheet.Paste
>> ActiveSheet.ChartObjects(TextChartNum).Activate
>>' ** Runtime error 1004 - unable to get the ChartObjects property
>>' of the Worksheet class
>>' Note: second chart has been created where i wanted it, and
>>' it is selected.
>>'*That is to say, when it worked..i did a lot of mods and
>>' (as above) it is all messed up
>> Next ChartNum
>>End Sub
>
>
>
That would give two spaces; eg: "Chart 23".
Is that the secret...have two spaces?

How does one go about finding the name of a chart, changing it and
"resetting" that count (assuming ther is one)?
Back to top
Don Guillett
External


Since: Jan 04, 2006
Posts: 2988
PostPosted: Sat Apr 18, 2009 7:14 am    Post subject: Re: Excel inconsistency [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Sub getShapeNames()
For Each sh In ActiveSheet.Shapes
MsgBox sh.Name
Next sh
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1 RemoveThis @austin.rr.com
"Robert Baer" <robertbaer RemoveThis @localnet.com> wrote in message
news:7sudnfxkzuGB2nXUnZ2dnUVZ_vxi4p2d@posted.localnet...
> Bob Umlas wrote:
>
>> Quick glance shows you should probably replace:
>> TextChartNum = "Chart" + Str$(ChartNum)
>> with
>> TextChartNum = "Chart " + Str$(ChartNum)
>> (note the space after "Chart")
>>
>>
>> "Robert Baer" <robertbaer RemoveThis @localnet.com> wrote in message
>> news:irKdnQKHmsJ2OnvUnZ2dnUVZ_vOdnZ2d@posted.localnet...
>>
>>> After a bunch of fiddling, i got a macro to duplicate a chart below a
>>> single one on a worksheet.
>>> But there was a runtime error as noted near the end of the macro.
>>> So i did a lot of more fiddling and was able to get a number of copies,
>>> one after the other by calling them all "Chart 1".
>>> Nasty.
>>> Did other fiddling in attempts to change series ranges and title on the
>>> fly.
>>> Things got worse, so i tried to go back to the original macro as seen
>>> below.
>>> Even deleting the XLSTART folder and re-trying the macro did no good.
>>>**HELP**
>>>
>>>Sub Macro2()
>>>' Macro2 Macro
>>>' Macro recorded 4/15/2009 by Robert Baer
>>> Range("A1").Select
>>> SheetColumn = 1
>>> ActiveSheet.ChartObjects("Chart 1").Activate
>>>'*NOW FAILS-------------------^---NOW arg needs to be 1 not "Chart 1"
>>> For ChartNum = 1 To 20
>>> ActiveChart.ChartArea.Select
>>> ActiveChart.ChartArea.Copy
>>> ActiveChart.SeriesCollection(1).Select
>>> ActiveChart.SeriesCollection(2).Select
>>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>>>'*NOW FAILS------------------------------------^
>>> ActiveChart.Axes(xlValue, xlSecondary).Select
>>> ActiveChart.PlotArea.Select
>>> ActiveChart.ChartArea.Select
>>>'*NOTE redundant and useless duplication below*
>>> ActiveChart.SeriesCollection(1).Select
>>> ActiveChart.SeriesCollection(2).Select
>>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).Select
>>> ActiveChart.PlotArea.Select
>>> ActiveChart.ChartArea.Select
>>> ActiveChart.SeriesCollection(1).Select
>>> ActiveChart.SeriesCollection(2).Select
>>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).Select
>>> ActiveChart.PlotArea.Select
>>> ActiveChart.ChartArea.Select
>>> ActiveChart.SeriesCollection(1).Select
>>> ActiveChart.SeriesCollection(2).Select
>>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).Select
>>> ActiveChart.PlotArea.Select
>>> ActiveChart.ChartArea.Select
>>> ActiveChart.SeriesCollection(1).Select
>>> ActiveChart.SeriesCollection(2).Select
>>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).Select
>>> ActiveChart.PlotArea.Select
>>> ActiveChart.ChartArea.Select
>>> ActiveChart.SeriesCollection(1).Select
>>> ActiveChart.SeriesCollection(2).Select
>>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).Select
>>> ActiveChart.PlotArea.Select
>>> ActiveChart.ChartArea.Select
>>> ActiveChart.SeriesCollection(1).Select
>>> ActiveChart.SeriesCollection(2).Points(67).Select
>>> ActiveChart.Axes(xlCategory).MajorGridlines.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
>>> ActiveChart.Axes(xlValue, xlSecondary).Select
>>> ActiveChart.PlotArea.Select
>>> ActiveChart.ChartArea.Select
>>> ActiveWindow.Visible = False
>>> Windows("StripperWells.xls").Activate
>>> SheetColumn = SheetColumn + 21
>>> TextSheetColumn = "A" + LTrim(Str$(SheetColumn))
>>> ChartNum = ChartNum + 1
>>> TextChartNum = "Chart" + Str$(ChartNum)
>>> Range(TextSheetColumn).Select
>>> ActiveSheet.Paste
>>> ActiveSheet.ChartObjects(TextChartNum).Activate
>>>' ** Runtime error 1004 - unable to get the ChartObjects property
>>>' of the Worksheet class
>>>' Note: second chart has been created where i wanted it, and
>>>' it is selected.
>>>'*That is to say, when it worked..i did a lot of mods and
>>>' (as above) it is all messed up
>>> Next ChartNum
>>>End Sub
>>
>>
>>
> That would give two spaces; eg: "Chart 23".
> Is that the secret...have two spaces?
>
> How does one go about finding the name of a chart, changing it and
> "resetting" that count (assuming ther is one)?
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions All times are: Eastern Time (US & Canada) (change)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum