Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Loops
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,588
Default Loops
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" wrote in message ...
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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Loops
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" wrote in message ...
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





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,588
Default Loops
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" wrote in message ...
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" wrote in message ...
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







Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Loops
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" wrote in message ...
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" wrote in message ...
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








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Loops fugfug[_2_] Excel Programming 3 July 8th 05 10:53 AM
for each loops adncmm1980[_3_] Excel Programming 1 October 4th 04 12:56 PM
Loops Snow[_2_] Excel Programming 2 May 13th 04 09:48 PM
Loops etc. Jonathan Vickers Excel Programming 6 February 28th 04 05:35 PM
Loops Tom Ogilvy Excel Programming 0 July 18th 03 05:20 PM


All times are GMT +1. The time now is 04:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement