Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loops | Excel Programming | |||
for each loops | Excel Programming | |||
Loops | Excel Programming | |||
Loops etc. | Excel Programming | |||
Loops | Excel Programming |