Difficulty Setting Up Excel Chart With Scroll Capabilities

[Dave Marden]

I am trying to set up an excel graph that will let me scroll through a large
amount of values. I want to be able to set my range as something like
A1:H501 and actually have data all the way to Row 65000.

What I want to do is then have a scroll bar be manually moved and the chart
update according to the new area I want charted. The Code Below is what I
have tried and I keep getting errors. Any help would be appreciated.

I am grabbing data from an Programmable Logic Controller and the data is
spread over a 2 hour period, so I want to be able to scroll through the
data.

Dim LowerBound As Double
Dim UpperBound As Double
Dim CenterSelection As Double
Dim GraphRange(5000, 32) As Single

Public Sub SBarArea_Change()
GetValues
End Sub

Public Sub GetValues()
Dim OriginalArea As Range
CenterSelection = Range("A46").Value
LowerBound = CenterSelection - 250
UpperBound = CenterSelection + 250
MsgBox "Before Copying"
OriginalArea = Range("D" & LowerBound & ":" & "K" & UpperBound)
Range("M15:T515") = Range(OriginalArea)
MsgBox "After Copying"
LowerBound = 0
UpperBound = 0
End Sub

It Appears that excel doesn't like me making my data in OriginalArea
dynamic.

Any help would be appreciated,
Dave Marden

 

[Andy Pope]

Hi,

Try this revised code.

Public Sub GetValues()
Dim OriginalArea As String
CenterSelection = Range("A46").Value
LowerBound = CenterSelection - 250
UpperBound = CenterSelection + 250
MsgBox "Before Copying"

OriginalArea = _
Range("D" & LowerBound & ":" & "K" & UpperBound).Address
Range("M15:T515") = Range(OriginalArea).Value

MsgBox "After Copying"
LowerBound = 0
UpperBound = 0
End Sub

You might find the Named range approach easiser. No code required.
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

 

[Dave Marden]

Thanks Andy, That was a big help.

Dave

Hi,

Try this revised code.

Public Sub GetValues()
Dim OriginalArea As String
CenterSelection = Range("A46").Value
LowerBound = CenterSelection - 250
UpperBound = CenterSelection + 250
MsgBox "Before Copying"

OriginalArea = _
Range("D" & LowerBound & ":" & "K" & UpperBound).Address
Range("M15:T515") = Range(OriginalArea).Value

MsgBox "After Copying"
LowerBound = 0
UpperBound = 0
End Sub

You might find the Named range approach easiser. No code required.
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy