How to call variables in excel macro?

[doingcho2]

Hello,

I have never coded in visual basic before. I am trying to create a macro that will graph a ton of data. I made a quick program using the "record" function, then tried to mess with it to get the rest of the data graphed.
This is what I have come up with so far:

Sub Graphing2()
'
' Graphing2 Macro
'

'
    Let a = 176
    Let b = 126
    Let y = 3
    Let x = 0
    Do While x < 225
    ActiveSheet.ChartObjects("Chart 1").Activate
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=""Unit y"""
    ActiveChart.FullSeriesCollection(2).XValues = "=Sheet1!$B$a:$B$b"
    ActiveChart.FullSeriesCollection(2).Values = "=Sheet1!$E$a:$E$b"
    x = x + 1
    y = y + 1
    a = a + 67
    b = b + 67
    Loop
End Sub

I'm getting an error where I try and move the X and Y values down a couple of lines to the next set of data. Am I not calling the variables correctly? Im not sure whats wrong.
Any information is appreciated.

Thanks.

 

[Michael M]

Remove the word "Let" from the lines....should simply be
a = 176
b = 126
y = 3
x = 0
And you need to declare ALL of your variables.
Put this
Dim a as long, b as long, x as long, y as long
after the
' Graphing2 Macro line

 

[doingcho2]

Remove the word "Let" from the lines....should simply be
a = 176
b = 126
y = 3
x = 0
And you need to declare ALL of your variables.
Put this
Dim a as long, b as long, x as long, y as long
after the
' Graphing2 Macro line

Hello,

Thank you for the reply, I declared them all with Dim and set them as "Long" variables.
But I am still getting the error on line 17 when I am trying to move the X and Y values to graph different data.
How do I initialize the variables? Is this correct? And I am calling them correctly on lines 17 and 18?

Sub Graphing2()
'
' Graphing2 Macro
'

'
    Dim a, b, y, x As Long
    a = 176
    b = 126
    y = 3
    x = 0
    Do While x < 225
    ActiveSheet.ChartObjects("Chart 1").Activate
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=""Unit y"""
    ActiveChart.FullSeriesCollection(2).XValues = "=Sheet1!$B$a:$B$b"
    ActiveChart.FullSeriesCollection(2).Values = "=Sheet1!$E$a:$E$b"
    x = x + 1
    y = y + 1
    a = a + 67
    b = b + 67
    Loop
End Sub

 

[Norie]

What's the error message?

 

[doingcho2]

What's the error message?

The error message I am getting is:

Run-time error '1004':
Application-defined or object-defined error

 

[Norie]

I don't think that's caused by a problem with the variables, which line of code is triggering the error?

 

[doingcho2]

I don't think that's caused by a problem with the variables, which line of code is triggering the error?

ActiveChart.FullSeriesCollection(2).XValues = "=Sheet1!$B&a:$B&b"
I am getting the error on this line. I am guessing its from the "a" and "b". Am I calling them properly?

 

[Michael M]

The way you declared the variables is incorrect
Dim a, b, y, x As Long
Note the difference in the way I posted the line....
Dim a as long, b as long, x as long, y as long

 

[Norie]

Ah, it is connected with the variables.

This shouldn't error but I'm not sure it'll do what you want.
[code-vba]
ActiveChart.FullSeriesCollection(2).Name = "=""Unit " & y & """"
ActiveChart.FullSeriesCollection(2).XValues = "=Sheet1!$B$" & a & ":$B$" & b
ActiveChart.FullSeriesCollection(2).Values = "=Sheet1!$E$" & a & ":$E$" & b
[/code]