User Input Box for range of a chart

[Jedit]

I started creating a macro that asks the user to enter the number of rows into a message box. The message box needs to take that value and make the last row of the graph that value.

Dim myColm As Range
Set myColm = Application.InputBox("How many rows are there?", Type:=8)
On Error Resume Next
Selection.Formula = "=SERIES('Chart'!R9C34,,'Chart'!R10C34:R(mycolm)C34,6)"

I realize that mycolm is not supposed to be there but that is essentially what I would like the macro to have the value inserted. If anyone can offer me any advice or guidance, I would greatly appreciate it.

Jedit

 

[AlphaFrog]

Perhaps something like this...

Selection.Formula = "=SERIES('Chart'!R9C34,,'Chart'!R10C34:R" & mycolm & "C34,6)"

The formula is just a string where you can concatenate your variable within.

 

[Jedit]

Thank you very much for the feedback. I went back into my source code and entered the modifications you made to my code; however, I am still having trouble with my code.

I am added a banded graph to my line graph however, the line graph is either shorter or longer than the banded graph. The above macro is so the user can type a value into the input box which then makes the last row of the banded graph equal to the value inserted into the user input box. The code is as follows:
Dim myColm As Range
Set myColm = Application.InputBox("How many rows are there?", Type:=8)
On Error Resume Next
ActiveChart.SeriesCollection(6).Select
Selection.Formula = "=SERIES('Chart'!R9C34,,'Chart'!R10C34:R" & myColm & "C34,6)"
ActiveChart.SeriesCollection(5).Select
Selection.Formula = "=SERIES('Chart'!R9C33,,'Chart'!R10C34:R" & myColm & "C33,6)"
ActiveChart.SeriesCollection(4).Select
Selection.Formula = "=SERIES('Chart'!R9C32,,'Chart'!R10C34:R" & myColm & "C32,6)"
ActiveChart.SeriesCollection(3).Select
Selection.Formula = "=SERIES('Chart'!R9C31,,'Chart'!R10C34:R" & myColm & "C31,6)"
ActiveChart.SeriesCollection(2).Select
Selection.Formula = "=SERIES('Chart'!R9C30,,'Chart'!R10C34:R" & myColm & "C30,6)"
ActiveChart.SeriesCollection(1).Select
Selection.Formula = "=SERIES('Chart'!R9C29,,'Chart'!R10C34:R" & myColm & "C29,6)"


End Sub
If you could provide me any guidance as to where I am going wrong I would greatly appreciate it.

 

[AlphaFrog]

Maybe try this. I'm guessing though.

[color=darkblue]Dim[/color] myColm [color=darkblue]As[/color] [color=darkblue]Long[/color]
myColm = Application.InputBox("How many rows are there?", Type:=2)
[color=darkblue]If[/color] myColm = 0 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color] [color=darkblue]Else[/color] myColm = myColm [B]+ 10[/B]
[color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
ActiveChart.SeriesCollection(6).Formula = "=SERIES('Chart'!R9C34,,'Chart'!R10C34:R" & myColm & "C34,6)"
ActiveChart.SeriesCollection(5).Formula = "=SERIES('Chart'!R9C33,,'Chart'!R10C34:R" & myColm & "C33,6)"
ActiveChart.SeriesCollection(4).Formula = "=SERIES('Chart'!R9C32,,'Chart'!R10C34:R" & myColm & "C32,6)"
ActiveChart.SeriesCollection(3).Formula = "=SERIES('Chart'!R9C31,,'Chart'!R10C34:R" & myColm & "C31,6)"
ActiveChart.SeriesCollection(2).Formula = "=SERIES('Chart'!R9C30,,'Chart'!R10C34:R" & myColm & "C30,6)"
ActiveChart.SeriesCollection(1).Formula = "=SERIES('Chart'!R9C29,,'Chart'!R10C34:R" & myColm & "C29,6)"

Your original code was prompting for a range and not a number. Was that the "problem"?

Also, the series formula is expectiong a row number and not a row count. So since the first data row is 10, then add 10 to the prompted row count.

 

[Jedit]

Thank you very much you are a life saver. I knew that was one of my errors and I just finished cleaning up the rest of the code to make it work perfectly. Thank you very much for your assistance.