Hi,
I have been having a significant amount of difficulty in VBA trying to
select multiple rows for graphing. I am inputing the spreadsheet data
as output from a file (from which the inputs are also in another sheet)
and I need to graph some of the rows and columns. The rows are not
always together nor does the output always form the same number of rows
each time. For instance in some cases I want to graph from the range
B70 to B85 and sometimes from B70 to B74 depending on what the program
outputs. Aside from these I also need another column to be selected to
graph along the X and Y axes - so I need to select columns B70 and E70
to B85 and E85. I've tried using the offset function but Excel doesn't
seem to like it when I try to run a command like
Range("B70:B85").Select and Range("E70:E85"). Although I realize this
syntax does not make sense is there a way to select multiple columns
like this through a loop which searches for a delimiter so that the
range can be flexible each time? This would be particularly essential
since the program outputs several places for data on the same
spreadsheet and I would need to run this graphing function multiple
times for each iteration.
What sort of commands are useful for looping through a command like
Range.Select adding on to the selection each time through use of a Do
Until or For loop?
Thanks in advance for any help!

Andrew

You not have to select the range to loop

But try this

Range("B70:B85,E70:E85").Select




--
Regards Ron De Bruin
http://www.rondebruin.nl



wrote in message oups.com...
Hi,
I have been having a significant amount of difficulty in VBA trying to
select multiple rows for graphing. I am inputing the spreadsheet data
as output from a file (from which the inputs are also in another sheet)
and I need to graph some of the rows and columns. The rows are not
always together nor does the output always form the same number of rows
each time. For instance in some cases I want to graph from the range
B70 to B85 and sometimes from B70 to B74 depending on what the program
outputs. Aside from these I also need another column to be selected to
graph along the X and Y axes - so I need to select columns B70 and E70
to B85 and E85. I've tried using the offset function but Excel doesn't
seem to like it when I try to run a command like
Range("B70:B85").Select and Range("E70:E85"). Although I realize this
syntax does not make sense is there a way to select multiple columns
like this through a loop which searches for a delimiter so that the
range can be flexible each time? This would be particularly essential
since the program outputs several places for data on the same
spreadsheet and I would need to run this graphing function multiple
times for each iteration.
What sort of commands are useful for looping through a command like
Range.Select adding on to the selection each time through use of a Do
Until or For loop?
Thanks in advance for any help!

Andrew

Andrew,

Something like this, assuming that your sheet is named Sheet1:

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
With Worksheets("Sheet1")
ActiveChart.SetSourceData Source:=Union(.Range(.Range("B70"), .Range("B70").End(xlDown)), _
.Range(.Range("E70"), .Range("E70").End(xlDown)))
ActiveChart.Location Whe=xlLocationAsObject, Name:=.Name
End With

HTH,
Bernie
MS Excel MVP


wrote in message oups.com...
Hi,
I have been having a significant amount of difficulty in VBA trying to
select multiple rows for graphing. I am inputing the spreadsheet data
as output from a file (from which the inputs are also in another sheet)
and I need to graph some of the rows and columns. The rows are not
always together nor does the output always form the same number of rows
each time. For instance in some cases I want to graph from the range
B70 to B85 and sometimes from B70 to B74 depending on what the program
outputs. Aside from these I also need another column to be selected to
graph along the X and Y axes - so I need to select columns B70 and E70
to B85 and E85. I've tried using the offset function but Excel doesn't
seem to like it when I try to run a command like
Range("B70:B85").Select and Range("E70:E85"). Although I realize this
syntax does not make sense is there a way to select multiple columns
like this through a loop which searches for a delimiter so that the
range can be flexible each time? This would be particularly essential
since the program outputs several places for data on the same
spreadsheet and I would need to run this graphing function multiple
times for each iteration.
What sort of commands are useful for looping through a command like
Range.Select adding on to the selection each time through use of a Do
Until or For loop?
Thanks in advance for any help!

Andrew

Bernie,
I had tried something like you suggested but the problem with the
xlDown command is that it terminates at the first blank line, I need a
character delimiter to terminate since the last character in the string
is a '*'. I suppose I could insert cells above all the stars but the
program output is actually a notepad file that I'm placing into Excel
and separating into separate cells and there are *'s in many places
aroudn the file.
I do like your Union function however the xlDown command still presents
a problem.
Here's another thing, like I said I needed to graph multiple outputs of
a sheet so this Range("B70") wouldn't work if I wanted to rerun this
operation at the next available spot maybe B140.
I want to be able to do something like this:
i = j
Do Until inputcells(i, 1) = "*"
Range("(j,1):(i,1),(j,4):(i,4)").Select
i = i + 1
Loop
Where j is the initial starting point and i is the point down the
column and the second number corresponds to the specific column (0=A,
1=B, 2=C, etc...).
Even though I realize this is not correct I want to be able to loop
through these columns and as you can see this would be useful because I
could then plot these points and search for the next available set of
inputs and add them by using:
ActiveChart.SeriesCollection(1).XValues = "=Sheet20!R70C2:R84C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet20!R70C5:R84C5"
As you can see this creates a larger problem as well because the Series
collection is a string and I would have to collect my inputcells(x,y)
variable as a string.
Is there a way to run the loop I had suggested using that kind of
syntax or a modified version of it?


Bernie Deitrick wrote:
Andrew,

Something like this, assuming that your sheet is named Sheet1:

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
With Worksheets("Sheet1")
ActiveChart.SetSourceData Source:=Union(.Range(.Range("B70"), .Range("B70").End(xlDown)), _
.Range(.Range("E70"), .Range("E70").End(xlDown)))
ActiveChart.Location Whe=xlLocationAsObject, Name:=.Name
End With

HTH,
Bernie
MS Excel MVP


wrote in message oups.com...
Hi,
I have been having a significant amount of difficulty in VBA trying to
select multiple rows for graphing. I am inputing the spreadsheet data
as output from a file (from which the inputs are also in another sheet)
and I need to graph some of the rows and columns. The rows are not
always together nor does the output always form the same number of rows
each time. For instance in some cases I want to graph from the range
B70 to B85 and sometimes from B70 to B74 depending on what the program
outputs. Aside from these I also need another column to be selected to
graph along the X and Y axes - so I need to select columns B70 and E70
to B85 and E85. I've tried using the offset function but Excel doesn't
seem to like it when I try to run a command like
Range("B70:B85").Select and Range("E70:E85"). Although I realize this
syntax does not make sense is there a way to select multiple columns
like this through a loop which searches for a delimiter so that the
range can be flexible each time? This would be particularly essential
since the program outputs several places for data on the same
spreadsheet and I would need to run this graphing function multiple
times for each iteration.
What sort of commands are useful for looping through a command like
Range.Select adding on to the selection each time through use of a Do
Until or For loop?
Thanks in advance for any help!

Andrew

Andrew,

Something along these lines may work - if you want to find the first * below the selected cell -
(for example, select B70). (the cell that is set could also be a variable that is found
automatically, given a certain criterion):

Dim myR As Range
Dim myRow As Long

Set myR = ActiveCell

myRow = myR.EntireColumn.Find("~*", After:=myR, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
With Worksheets("Sheet1")
ActiveChart.SetSourceData Source:=Union(.Range(myR, .Cells(myRow, myR.Column)), _
.Range(.Cells(myR.Row, myR.Column + 3), .Cells(myRow, myR.Column + 3)))
ActiveChart.Location Whe=xlLocationAsObject, Name:=.Name
End With

HTH,
Bernie
MS Excel MVP


wrote in message ups.com...
Bernie,
I had tried something like you suggested but the problem with the
xlDown command is that it terminates at the first blank line, I need a
character delimiter to terminate since the last character in the string
is a '*'. I suppose I could insert cells above all the stars but the
program output is actually a notepad file that I'm placing into Excel
and separating into separate cells and there are *'s in many places
aroudn the file.
I do like your Union function however the xlDown command still presents
a problem.
Here's another thing, like I said I needed to graph multiple outputs of
a sheet so this Range("B70") wouldn't work if I wanted to rerun this
operation at the next available spot maybe B140.
I want to be able to do something like this:
i = j
Do Until inputcells(i, 1) = "*"
Range("(j,1):(i,1),(j,4):(i,4)").Select
i = i + 1
Loop
Where j is the initial starting point and i is the point down the
column and the second number corresponds to the specific column (0=A,
1=B, 2=C, etc...).
Even though I realize this is not correct I want to be able to loop
through these columns and as you can see this would be useful because I
could then plot these points and search for the next available set of
inputs and add them by using:
ActiveChart.SeriesCollection(1).XValues = "=Sheet20!R70C2:R84C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet20!R70C5:R84C5"
As you can see this creates a larger problem as well because the Series
collection is a string and I would have to collect my inputcells(x,y)
variable as a string.
Is there a way to run the loop I had suggested using that kind of
syntax or a modified version of it?


Bernie Deitrick wrote:
Andrew,

Something like this, assuming that your sheet is named Sheet1:

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
With Worksheets("Sheet1")
ActiveChart.SetSourceData Source:=Union(.Range(.Range("B70"), .Range("B70").End(xlDown)), _
.Range(.Range("E70"), .Range("E70").End(xlDown)))
ActiveChart.Location Whe=xlLocationAsObject, Name:=.Name
End With

HTH,
Bernie
MS Excel MVP


wrote in message oups.com...
Hi,
I have been having a significant amount of difficulty in VBA trying to
select multiple rows for graphing. I am inputing the spreadsheet data
as output from a file (from which the inputs are also in another sheet)
and I need to graph some of the rows and columns. The rows are not
always together nor does the output always form the same number of rows
each time. For instance in some cases I want to graph from the range
B70 to B85 and sometimes from B70 to B74 depending on what the program
outputs. Aside from these I also need another column to be selected to
graph along the X and Y axes - so I need to select columns B70 and E70
to B85 and E85. I've tried using the offset function but Excel doesn't
seem to like it when I try to run a command like
Range("B70:B85").Select and Range("E70:E85"). Although I realize this
syntax does not make sense is there a way to select multiple columns
like this through a loop which searches for a delimiter so that the
range can be flexible each time? This would be particularly essential
since the program outputs several places for data on the same
spreadsheet and I would need to run this graphing function multiple
times for each iteration.
What sort of commands are useful for looping through a command like
Range.Select adding on to the selection each time through use of a Do
Until or For loop?
Thanks in advance for any help!

Andrew

Bernie,
That is really quite nice. However it requires the first column to be
selected before it is run - you cannot simply activate cell B70 - it
requires the range of cells B70:B85 to be selected in order to do this.
Here is my attempt at a correction - however I'm getting an
overflow...any suggestions?

Dim myR, x%, inputcells As Range
Dim myRow As Long

Set myR = ActiveCell
x = ActiveCell
Set inputcells = Range("A70")
Do Until inputcells(x, 2) = "~*"
x = x + 1
Loop

myRow = myR.EntireColumn.Find("~*", After:=myR, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
Range(myR, x - 1).Select

My code DOESN'T require you to select the first column - it is all based on the active cell.

A lot of your code is bad:

This dimensionsw myR and x as variants:
Dim myR, x%, inputcells As Range

This sets the value of x to the activecell's value, not to the cell itself:
x = ActiveCell

So, if the activecell's value is 100, the search is started 100 rows down from the activecell...

Set inputcells = Range("A70")
Do Until inputcells(x, 2) = "~*"
x = x + 1
Loop

And since x is not a range object, this won't work:

Range(myR, x - 1).Select

Not sure what it is that you want to do beyond what I had shown....

HTH,
Bernie
MS Excel MVP


wrote in message ups.com...
Bernie,
That is really quite nice. However it requires the first column to be
selected before it is run - you cannot simply activate cell B70 - it
requires the range of cells B70:B85 to be selected in order to do this.
Here is my attempt at a correction - however I'm getting an
overflow...any suggestions?

Dim myR, x%, inputcells As Range
Dim myRow As Long

Set myR = ActiveCell
x = ActiveCell
Set inputcells = Range("A70")
Do Until inputcells(x, 2) = "~*"
x = x + 1
Loop

myRow = myR.EntireColumn.Find("~*", After:=myR, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
Range(myR, x - 1).Select

Bernie,
It appears as if I'm slightly confused. However, in your code if you
select a particular cell then it does the requested operation however I
have about 25 separate series when graphed. I wasn't exactly sure how
to repair this and the only way I could get a single graph with one
series was to select the column with the particular cells I needed (all
of them). Perhaps it is one of my settings that causes so many series
to appear?
Thanks,
Andrew