[sapcons]

I have written a macro which will graph multiple data. However, I can't get the syntax correct for a variable cell selection for naming the series of the chart or the title of each chart.

ActiveChart.SeriesCollection(1).name = ???

I have tried active cell select, setting range,....

Thanks for any hints and help you can give.

-C

[cristobal03]

Hey there, welcome to TSG.

Could you post the code so we can see what's going on? If you click on the Post a Reply! button beneath this post, paste your code into the reply window, highlight it, and click the octothorpe icon (#...it's next to the Font formatting controls just above the post editor), the code's indentation will be preserved.

Or, paste the code into the Quick Reply window and place code tags before and after the code, like so:

[ code ]
There are four spaces preceding this line that aren't preserved.
[ /code ]

    By removing the spaces from the above tags, the four spaces
    preceding these lines are preserved.

It isn't necessary to use the code tags, just easier to read.

The point being, hey, can you post the macro?

chris.

[sapcons]

Charts.Add
ActiveChart.ChartType = xlLineMarkers

ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "='Sec 9'!R7C1:R37C1"
ActiveChart.SeriesCollection(2).XValues = "='Sec 9'!R7C1:R37C1"
ActiveChart.SeriesCollection(3).XValues = "='Sec 9'!R7C1:R37C1"
ActiveChart.SeriesCollection(4).XValues = "='Sec 9'!R7C1:R37C1"
'Trouble starts here....
Set cell = Sheets("Sec 9").Range(5, m)
ActiveChart.SeriesCollection(1).name = ActiveCell.Value
Set cell = Sheets("Sec 9").Range(5, m + 6)
ActiveChart.SeriesCollection(2).name = ActiveCell.Value
Set cell = Sheets("Sec 9").Range(5, m + 12)
ActiveChart.SeriesCollection(3).name = ActiveCell.Value
Set cell = Sheets("Sec 9").Range(5, m + 18)
ActiveChart.SeriesCollection(4).name = ActiveCell.Value

The xvalues are constant by the series names change with the data selection.
Thanks!

[cristobal03]

Is that the entire routine?

chris.

[cristobal03]

In the meantime, I'd like to impart a couple of tips that will make your code cleaner and easier to develop.

First, there is very little reason to select--or in this case, set as an object--a cell to grab its value. It adds unnecessary burden on the processor and is ambiguous more often than not (vis-a-vis, which cell is currently represented by the ActiveCell object?). Because you already refer to the Range object to set the Cell object, just use the Range by itself without setting any objects; the Range object already contains the cell's value.

Second,

...and my time's up, I'll finish my thoughts when I get home.

to be continued...

chris.

[sapcons]

The beginning selects the data range, which is working fine, and the ending is just formatting the chart, then a do loop

[cristobal03]

It'd help to see the whole routine, particularly because you're using a variable (m) in the posted code that isn't dimensioned or assigned anywhere within the code we can see.

chris.

[cristobal03]

Anyway, back to what I was saying.

Your code doesn't take advantage of the more robust logical controls available to VBA. By that I mean, each line of your code is a stand-alone statement--very straightforward, very clear but difficult to maintain. Suppose, for example, you didn't want to work with the ActiveChart object but rather some other chart. You'd have to change 10 lines of code (at least--there may be more in the undisclosed code).

VBA is OBL (Object-Based Language...some might disagree with me on that, but I'll stand by it) and is therefore a structured language. In other words, the language is designed to be condusive to logical structure; to that end, the object library provides several logical controls to define sections of code. The Do...Loop you mentioned is just one example of such a control. Other logical controls include For...Each loops, While...Wend statements, With blocks, If...Then and Select Case conditional blocks, and so forth. In fact, Dim, Set, and Let are also logical controls, as are routine declarations and even modules.

I know that's a lot to just toss out there, and if it's all Greek don't worry. I and others at TSG will be more than happy to help you with the finer points if you need it--and we'll be thankful to hear of any tips or tricks you discover. My point is simply this: if you're interested in improving as a programmer, I'd like to have the opportunity to streamline your macro and explain to you what I'm doing and why I'm doing it, so that you may get a better understanding of why the code does what it does and why clean code is so much more desirable than spaghetti code.

Please don't be offended; I learned a great deal about programming, VBA, and a few other languages by studying and listening to the advice of other programmers, so I'm always eager for the opportunity to help others with their code. I certainly hope I haven't deterred you from continuing to post at TSG.

Anyway, if you please, the whole routine; or, at the very least, an explanation of that unspecified variable m.

chris.

[sapcons]

Oh no offense taken at all! I am self taught, so eveyrthing comes in steps to me. I wil take all the lessons/advice I can get. Here is my entire code.

b = 6 ' first column of fluid level data
x = 2 'name of series, name of well
j = 2 'title of graph, name of location
Sheets("Sec 9").Activate

1: 'Selects multiple columns of data



Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range, myMultiAreaRange As Range
Worksheets("Sec 9").Activate
Set r1 = Range(Cells(7, 1), Cells(37, 1))
Set r2 = Range(Cells(7, b), Cells(37, b))
Set r3 = Range(Cells(7, b + 6), Cells(37, b + 6))
Set r4 = Range(Cells(7, b + 12), Cells(37, b + 12))
Set r5 = Range(Cells(7, b + 18), Cells(37, b + 18))

Set myMultiAreaRange = Union(r1, r2, r3, r4, r5)
myMultiAreaRange.Select

Charts.Add
ActiveChart.ChartType = xlLineMarkers

ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "='Sec 9'!R7C1:R37C1"
ActiveChart.SeriesCollection(2).XValues = "='Sec 9'!R7C1:R37C1"
ActiveChart.SeriesCollection(3).XValues = "='Sec 9'!R7C1:R37C1"
ActiveChart.SeriesCollection(4).XValues = "='Sec 9'!R7C1:R37C1"

ActiveChart.SeriesCollection(1).Name = "='Sec 9'!R5C2"
ActiveChart.SeriesCollection(2).Name = "='Sec 9'!R5C8"
ActiveChart.SeriesCollection(3).Name = "='Sec 9'!R5C14"
ActiveChart.SeriesCollection(4).Name = "='Sec 9'!R5C20"

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="='Range(Cells(4, j))"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Range(Cells(4, j))" & "Fluid Levels"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Day of Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Fluid Level"
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 4
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = 4
.MarkerStyle = xlX
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.ColorIndex = 42
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = 42
.MarkerStyle = xlStar
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 36
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.Axes(xlCategory).Select
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 5
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = 5
.MarkerStyle = xlTriangle
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Left = 10
Selection.Top = 195
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Left = 9
Selection.Top = 196
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

b = b + 24
x = x + 24
j = j + 24

GoTo 1
End Sub

All comments, help, anythings are welcome on my end! I am just happy to have found you!

Sincerely,
Cathy

[sapcons]

This is my original code.

Trying different codes to allow for naming several charts, I then added m=2 to be the series name of the each chart.

In a nut shell, this is what I am trying to do.

I have four columns of data for each well and I have 50 wells all on one spreadsheet
I want to code an automation for creating a graph for each well with the four columns of data set as series with the series name to be the label at the top of each column, and the title and chart name to be the corresponding well name. I can get the chart to generate but not the labeling to work.

Thanks again

[cristobal03]

I'm going to put this in code tags in case anybody else feels like jumping in.

Sub SomeSub() ' a generic name I invented
  b = 6 ' first column of fluid level data
  x = 2 'name of series, name of well
  j = 2 'title of graph, name of location

  Sheets("Sec 9").Activate

1: 'Selects multiple columns of data

  Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range, myMultiAreaRange As Range

  Worksheets("Sec 9").Activate

  Set r1 = Range(Cells(7, 1), Cells(37, 1))
  Set r2 = Range(Cells(7, b), Cells(37, b))
  Set r3 = Range(Cells(7, b + 6), Cells(37, b + 6))
  Set r4 = Range(Cells(7, b + 12), Cells(37, b + 12))
  Set r5 = Range(Cells(7, b + 18), Cells(37, b + 18))

  Set myMultiAreaRange = Union(r1, r2, r3, r4, r5)

  myMultiAreaRange.Select

  Charts.Add
  ActiveChart.ChartType = xlLineMarkers

  ActiveChart.SeriesCollection(1).Delete
  ActiveChart.SeriesCollection(1).XValues = "='Sec 9'!R7C1:R37C1"
  ActiveChart.SeriesCollection(2).XValues = "='Sec 9'!R7C1:R37C1"
  ActiveChart.SeriesCollection(3).XValues = "='Sec 9'!R7C1:R37C1"
  ActiveChart.SeriesCollection(4).XValues = "='Sec 9'!R7C1:R37C1"

  ActiveChart.SeriesCollection(1).Name = "='Sec 9'!R5C2"
  ActiveChart.SeriesCollection(2).Name = "='Sec 9'!R5C8"
  ActiveChart.SeriesCollection(3).Name = "='Sec 9'!R5C14"
  ActiveChart.SeriesCollection(4).Name = "='Sec 9'!R5C20"

  ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="='Range(Cells(4, j))"

  With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Range(Cells(4, j))" & "Fluid Levels"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Day of Month"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Fluid Level"
  End With

  ActiveChart.HasLegend = True
  ActiveChart.Legend.Select

  Selection.Position = xlBottom
  ActiveChart.PlotArea.Select
  ActiveChart.SeriesCollection(3).Select

  With Selection.Border
    .ColorIndex = 4
    .Weight = xlThin
    .LineStyle = xlContinuous
  End With

  With Selection
    .MarkerBackgroundColorIndex = xlNone
    .MarkerForegroundColorIndex = 4
    .MarkerStyle = xlX
    .Smooth = False
    .MarkerSize = 5
    .Shadow = False
  End With

  ActiveChart.SeriesCollection(4).Select

  With Selection.Border
    .ColorIndex = 42
    .Weight = xlThin
    .LineStyle = xlContinuous
  End With

  With Selection
    .MarkerBackgroundColorIndex = xlNone
    .MarkerForegroundColorIndex = 42
    .MarkerStyle = xlStar
    .Smooth = False
    .MarkerSize = 5
    .Shadow = False
  End With

  ActiveChart.PlotArea.Select

  With Selection.Border
    .ColorIndex = 16
    .Weight = xlThin
    .LineStyle = xlContinuous
  End With

  With Selection.Interior
    .ColorIndex = 36
    .PatternColorIndex = 1
    .Pattern = xlSolid
  End With

  ActiveChart.Axes(xlCategory).Select
  ActiveChart.SeriesCollection(2).Select

  With Selection.Border
    .ColorIndex = 5
    .Weight = xlThin
    .LineStyle = xlContinuous
  End With

  With Selection
    .MarkerBackgroundColorIndex = xlNone
    .MarkerForegroundColorIndex = 5
    .MarkerStyle = xlTriangle
    .Smooth = False
    .MarkerSize = 5
    .Shadow = False
  End With

  ActiveChart.Axes(xlValue).AxisTitle.Select

  Selection.Left = 10
  Selection.Top = 195

  ActiveChart.Axes(xlValue).Select
  ActiveChart.Axes(xlValue).AxisTitle.Select

  Selection.Left = 9
  Selection.Top = 196
  Selection.AutoScaleFont = True

  With Selection.Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 8
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .Background = xlAutomatic
  End With

  b = b + 24
  x = x + 24
  j = j + 24

  GoTo 1
End Sub

Alright, that's that.

Now.

I'm going to give you a research project while I write up some notes using examples from your code. Run a Google search on "spaghetti code" and read some articles about why GoTo statements are undesirable, and possible alternatives to GoTo construction. (You don't have to do this, of course, but if you're interested in improving your programming skills, this is one of the fundamental steps.)

I'm going to see if I can't come up with a "lesson plan" of sorts to get you on the right track toward accomplishing your initial requirement.

Or, if you prefer, I can just post the code that will work. Let me know if that's the case and I'll set you up.

chris.

[cristobal03]

[bump]

Okay, here's a tentative outline of some topics I'd like to discuss. I think the plan will be, I'll give you a code model you can use for now, and then we'll keep this thread open using both routines to discuss some fundamental programming areas.

  Lesson 1: Design Concepts
  Lesson 2: OOP, Objects, & Working with Objects
  Lesson 3: Algorithms & Structured Languages
  Lesson 4: Declaring, Dimensioning, and Initializing
               Object Variables in VBA
  Lesson 5: Control Structures: Loops
  Lesson 6: Control Structures: Conditional Statements
  Lesson 7: Good Practice: Comments, Clean Code, & Error Handling

Those are what I would consider important topics for beginner to intermediate VBA programmers. Now, I am myself mostly self-taught, so I'm not an expert about a lot of this stuff. But maybe I can direct you to some good resources that will improve upon what little information I can give you.

I'll bump back a working code model, let me know if you're interested in the other.

chris.

[sapcons]

Happy New Year Chris! Sorry for the delay, I have been on vacation.

I am interested in everything.
Yes, I do need a working code to finish my project.
I DO want to do the lessons too.

I appreciate this very much.

Cathy

[cristobal03]

Hey there, I wasn't sure if you'd be back or not

You ought to be able to attach a file by now. If you can, post a copy of the file in question so we have something more substantial to work with. (There are instructions in my signature under the link Posting Attachments if you need more information.)

I put this project aside because I wasn't sure if you were interested--I thought I might have scared you off --but since you are, I will get back to it this evening en force.

chris.

[sapcons]

What file is it that you would like me to attach?

Thanks - is this what you do for a career or is this just your way for fun?