Loops

Is it possible in Excel VBA to do something like the following
for cntr = 0 to 3

Series_Name_[cntr] = Pri_Series_Name_[cntr]

loop

to copy Pri_Series_Name_1 to Series_Name_1
and Pri_Series_Name_2 to Series_Name_2
and Pri_Series_Name_3 to Series_Name_3

Would much appreciate some help on this as I have a fair amount of similar
actions to do.

Thanks

Alec

Series_Name_[cntr] is a range name ?

Range("Series_Name_" & cntr).Copy Range("Series_Name_" & cntr)

Better to qualify the ranges with the worksheets, but you get the idea.

--
Tim Williams
Palo Alto, CA


"alecbowman" <[email protected]> wrote in message news:[email protected]...
> Is it possible in Excel VBA to do something like the following
> for cntr = 0 to 3
>
> Series_Name_[cntr] = Pri_Series_Name_[cntr]
>
> loop
>
> to copy Pri_Series_Name_1 to Series_Name_1
> and Pri_Series_Name_2 to Series_Name_2
> and Pri_Series_Name_3 to Series_Name_3
>
> Would much appreciate some help on this as I have a fair amount of similar
> actions to do.
>
> Thanks
>
> Alec
>
>

Thanks Tim,

This was a generalised query as I need to use the same sort of approach in
several places. The first is that I have a subroutine to add a chart series
to an active chart. I would like to be able to refer to the source data more
efficiently. )This isselected via a series of CheckBoxes originally).



Sub main()

Pri_Chart_Source_Sheet_1 = "SynchroELEVATIONSYNCHROTELLBACK"
Pri_Chart_Source_Sheet_2 = "SynchroELEVATIONSYNCHROTELLBACK"
Pri_Chart_Source_Sheet_3 = "SynchroELEVATIONSYNCHROTELLBACK"
Pri_Series_Data_Column_1 = "G"
Pri_Series_Data_Column_2 = "H"
Pri_Series_Data_Column_3 = "I"
Pri_Timestamp_Data_Column_1 = "B"
Pri_Timestamp_Data_Column_2 = "B"
Pri_Timestamp_Data_Column_3 = "B"
Last_Row_of_Pri_Source_Sheet_1 = 10916
Last_Row_of_Pri_Source_Sheet_2 = 10916
Last_Row_of_Pri_Source_Sheet_3 = 10916

For Series_Index_No = 2 To 20

Add_Chart_Series

Next


End Sub

Sub Add_Chart_Series()

If Pri_Series_Data_Column_1 <> Empty Then
With ActiveChart
Set PlotValuesSeries = Sheets("Pri_Chart_Source_Sheet_" &
Series_Index_No).Range(Pri_Series_Data_Column_1 & "7:" &
Pri_Series_Data_Column_1 & Last_Row_of_Pri_Source_Sheet_1)
Set PlotXValuesSeries = Sheets("Pri_Chart_Source_Sheet_" &
Series_Index_No).Range(Pri_Timestamp_Data_Column_1 & "7:" &
Pri_Timestamp_Data_Column_1 & Last_Row_of_Pri_Source_Sheet_1)
.SeriesCollection.Add Sheets("Pri_Chart_Source_Sheet_" &
Series_Index_No).Range(Pri_Series_Data_Column_1 & "7:" &
Pri_Series_Data_Column_1 & Last_Row_of_Pri_Source_Sheet_1)
.SeriesCollection(Series_Index_No).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MarkerStyle = xlNone
End With
With Sheets("Pri_Chart_Source_Sheet_" & Series_Index_No)
Chart.SeriesCollection(Series_Index_No).XValues =
PlotXValuesSeries
Chart.SeriesCollection(Series_Index_No).Values =
PlotValuesSeries
End With
End With
End If

End Sub

This code falls over with a Subscript out of range error on the following
line;

Set PlotValuesSeries = Sheets("Pri_Chart_Source_Sheet_" &
Series_Index_No).Range(Pri_Series_Data_Column_1 & "7:" &
Pri_Series_Data_Column_1 & Last_Row_of_Pri_Source_Sheet_1)


Would much appreciate some help on this.

Alec Bowman

"Tim Williams" wrote:

> Series_Name_[cntr] is a range name ?
>
> Range("Series_Name_" & cntr).Copy Range("Series_Name_" & cntr)
>
> Better to qualify the ranges with the worksheets, but you get the idea.
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "alecbowman" <[email protected]> wrote in message news:[email protected]...
> > Is it possible in Excel VBA to do something like the following
> > for cntr = 0 to 3
> >
> > Series_Name_[cntr] = Pri_Series_Name_[cntr]
> >
> > loop
> >
> > to copy Pri_Series_Name_1 to Series_Name_1
> > and Pri_Series_Name_2 to Series_Name_2
> > and Pri_Series_Name_3 to Series_Name_3
> >
> > Would much appreciate some help on this as I have a fair amount of similar
> > actions to do.
> >
> > Thanks
> >
> > Alec
> >
> >
>
>
>

> Sheets("Pri_Chart_Source_Sheet_" & Series_Index_No)

You can't use this type of approach with a variable name. You could try putting the sheet names into an array or a dictionary and
referencing them

Sheets(arrChartSourceSheet(Series_Index_No))

Are all of your variables defined at the module scope? Otherwise I'm not sure how Add_Chart_Series references the values...


Tim


"alecbowman" <[email protected]> wrote in message news:[email protected]...
> Thanks Tim,
>
> This was a generalised query as I need to use the same sort of approach in
> several places. The first is that I have a subroutine to add a chart series
> to an active chart. I would like to be able to refer to the source data more
> efficiently. )This isselected via a series of CheckBoxes originally).
>
>
>
> Sub main()
>
> Pri_Chart_Source_Sheet_1 = "SynchroELEVATIONSYNCHROTELLBACK"
> Pri_Chart_Source_Sheet_2 = "SynchroELEVATIONSYNCHROTELLBACK"
> Pri_Chart_Source_Sheet_3 = "SynchroELEVATIONSYNCHROTELLBACK"
> Pri_Series_Data_Column_1 = "G"
> Pri_Series_Data_Column_2 = "H"
> Pri_Series_Data_Column_3 = "I"
> Pri_Timestamp_Data_Column_1 = "B"
> Pri_Timestamp_Data_Column_2 = "B"
> Pri_Timestamp_Data_Column_3 = "B"
> Last_Row_of_Pri_Source_Sheet_1 = 10916
> Last_Row_of_Pri_Source_Sheet_2 = 10916
> Last_Row_of_Pri_Source_Sheet_3 = 10916
>
> For Series_Index_No = 2 To 20
>
> Add_Chart_Series
>
> Next
>
>
> End Sub
>
> Sub Add_Chart_Series()
>
> If Pri_Series_Data_Column_1 <> Empty Then
> With ActiveChart
> Set PlotValuesSeries = Sheets("Pri_Chart_Source_Sheet_" &
> Series_Index_No).Range(Pri_Series_Data_Column_1 & "7:" &
> Pri_Series_Data_Column_1 & Last_Row_of_Pri_Source_Sheet_1)
> Set PlotXValuesSeries = Sheets("Pri_Chart_Source_Sheet_" &
> Series_Index_No).Range(Pri_Timestamp_Data_Column_1 & "7:" &
> Pri_Timestamp_Data_Column_1 & Last_Row_of_Pri_Source_Sheet_1)
> .SeriesCollection.Add Sheets("Pri_Chart_Source_Sheet_" &
> Series_Index_No).Range(Pri_Series_Data_Column_1 & "7:" &
> Pri_Series_Data_Column_1 & Last_Row_of_Pri_Source_Sheet_1)
> .SeriesCollection(Series_Index_No).Select
> With Selection.Border
> .Weight = xlHairline
> .LineStyle = xlAutomatic
> End With
> With Selection
> .MarkerStyle = xlNone
> End With
> With Sheets("Pri_Chart_Source_Sheet_" & Series_Index_No)
> Chart.SeriesCollection(Series_Index_No).XValues =
> PlotXValuesSeries
> Chart.SeriesCollection(Series_Index_No).Values =
> PlotValuesSeries
> End With
> End With
> End If
>
> End Sub
>
> This code falls over with a Subscript out of range error on the following
> line;
>
> Set PlotValuesSeries = Sheets("Pri_Chart_Source_Sheet_" &
> Series_Index_No).Range(Pri_Series_Data_Column_1 & "7:" &
> Pri_Series_Data_Column_1 & Last_Row_of_Pri_Source_Sheet_1)
>
>
> Would much appreciate some help on this.
>
> Alec Bowman
>
> "Tim Williams" wrote:
>
>> Series_Name_[cntr] is a range name ?
>>
>> Range("Series_Name_" & cntr).Copy Range("Series_Name_" & cntr)
>>
>> Better to qualify the ranges with the worksheets, but you get the idea.
>>
>> --
>> Tim Williams
>> Palo Alto, CA
>>
>>
>> "alecbowman" <[email protected]> wrote in message news:[email protected]...
>> > Is it possible in Excel VBA to do something like the following
>> > for cntr = 0 to 3
>> >
>> > Series_Name_[cntr] = Pri_Series_Name_[cntr]
>> >
>> > loop
>> >
>> > to copy Pri_Series_Name_1 to Series_Name_1
>> > and Pri_Series_Name_2 to Series_Name_2
>> > and Pri_Series_Name_3 to Series_Name_3
>> >
>> > Would much appreciate some help on this as I have a fair amount of similar
>> > actions to do.
>> >
>> > Thanks
>> >
>> > Alec
>> >
>> >
>>
>>
>>

Tim,

Sadly, I had come to this conclusion as well.

I'm just an old hardware engineer used to machine code etc so often try to
do stupid things in VBA!

The attached code was (hacked around) part of a more complex program which
defines these variables as public.

I need to pass 4 variables to define each chart series. It looks like a
parameter array might be the the best way from what I have found in the
"help" but is it possible to set up a 2 dimension paramarray e.g. 4 x 20?

Many thanks

Alec

"Tim Williams" wrote:

> > Sheets("Pri_Chart_Source_Sheet_" & Series_Index_No)
>
> You can't use this type of approach with a variable name. You could try putting the sheet names into an array or a dictionary and
> referencing them
>
> Sheets(arrChartSourceSheet(Series_Index_No))
>
> Are all of your variables defined at the module scope? Otherwise I'm not sure how Add_Chart_Series references the values...
>
>
> Tim
>
>
> "alecbowman" <[email protected]> wrote in message news:[email protected]...
> > Thanks Tim,
> >
> > This was a generalised query as I need to use the same sort of approach in
> > several places. The first is that I have a subroutine to add a chart series
> > to an active chart. I would like to be able to refer to the source data more
> > efficiently. )This isselected via a series of CheckBoxes originally).
> >
> >
> >
> > Sub main()
> >
> > Pri_Chart_Source_Sheet_1 = "SynchroELEVATIONSYNCHROTELLBACK"
> > Pri_Chart_Source_Sheet_2 = "SynchroELEVATIONSYNCHROTELLBACK"
> > Pri_Chart_Source_Sheet_3 = "SynchroELEVATIONSYNCHROTELLBACK"
> > Pri_Series_Data_Column_1 = "G"
> > Pri_Series_Data_Column_2 = "H"
> > Pri_Series_Data_Column_3 = "I"
> > Pri_Timestamp_Data_Column_1 = "B"
> > Pri_Timestamp_Data_Column_2 = "B"
> > Pri_Timestamp_Data_Column_3 = "B"
> > Last_Row_of_Pri_Source_Sheet_1 = 10916
> > Last_Row_of_Pri_Source_Sheet_2 = 10916
> > Last_Row_of_Pri_Source_Sheet_3 = 10916
> >
> > For Series_Index_No = 2 To 20
> >
> > Add_Chart_Series
> >
> > Next
> >
> >
> > End Sub
> >
> > Sub Add_Chart_Series()
> >
> > If Pri_Series_Data_Column_1 <> Empty Then
> > With ActiveChart
> > Set PlotValuesSeries = Sheets("Pri_Chart_Source_Sheet_" &
> > Series_Index_No).Range(Pri_Series_Data_Column_1 & "7:" &
> > Pri_Series_Data_Column_1 & Last_Row_of_Pri_Source_Sheet_1)
> > Set PlotXValuesSeries = Sheets("Pri_Chart_Source_Sheet_" &
> > Series_Index_No).Range(Pri_Timestamp_Data_Column_1 & "7:" &
> > Pri_Timestamp_Data_Column_1 & Last_Row_of_Pri_Source_Sheet_1)
> > .SeriesCollection.Add Sheets("Pri_Chart_Source_Sheet_" &
> > Series_Index_No).Range(Pri_Series_Data_Column_1 & "7:" &
> > Pri_Series_Data_Column_1 & Last_Row_of_Pri_Source_Sheet_1)
> > .SeriesCollection(Series_Index_No).Select
> > With Selection.Border
> > .Weight = xlHairline
> > .LineStyle = xlAutomatic
> > End With
> > With Selection
> > .MarkerStyle = xlNone
> > End With
> > With Sheets("Pri_Chart_Source_Sheet_" & Series_Index_No)
> > Chart.SeriesCollection(Series_Index_No).XValues =
> > PlotXValuesSeries
> > Chart.SeriesCollection(Series_Index_No).Values =
> > PlotValuesSeries
> > End With
> > End With
> > End If
> >
> > End Sub
> >
> > This code falls over with a Subscript out of range error on the following
> > line;
> >
> > Set PlotValuesSeries = Sheets("Pri_Chart_Source_Sheet_" &
> > Series_Index_No).Range(Pri_Series_Data_Column_1 & "7:" &
> > Pri_Series_Data_Column_1 & Last_Row_of_Pri_Source_Sheet_1)
> >
> >
> > Would much appreciate some help on this.
> >
> > Alec Bowman
> >
> > "Tim Williams" wrote:
> >
> >> Series_Name_[cntr] is a range name ?
> >>
> >> Range("Series_Name_" & cntr).Copy Range("Series_Name_" & cntr)
> >>
> >> Better to qualify the ranges with the worksheets, but you get the idea.
> >>
> >> --
> >> Tim Williams
> >> Palo Alto, CA
> >>
> >>
> >> "alecbowman" <[email protected]> wrote in message news:[email protected]...
> >> > Is it possible in Excel VBA to do something like the following
> >> > for cntr = 0 to 3
> >> >
> >> > Series_Name_[cntr] = Pri_Series_Name_[cntr]
> >> >
> >> > loop
> >> >
> >> > to copy Pri_Series_Name_1 to Series_Name_1
> >> > and Pri_Series_Name_2 to Series_Name_2
> >> > and Pri_Series_Name_3 to Series_Name_3
> >> >
> >> > Would much appreciate some help on this as I have a fair amount of similar
> >> > actions to do.
> >> >
> >> > Thanks
> >> >
> >> > Alec
> >> >
> >> >
> >>
> >>
> >>
>
>
>