Dynamic Range for Charting using VBA

[Civilr Star]

Ok,

Objective: To create a dynamic range for charting that uses 2 form control scroll bars (pan & zoom).

Inputs: Pan value (cell linked), Zoom value (cell linked), offset (user input), range of data (user input range of cells on another sheet)

Output: The range of data that is controlled by the pan and zoom displayed on a chart.

What I've done: I created some simple nested if statements to limit the data so that when the range of data exceeds the max or min number of values the zoom and pan values are restricted. The put that back into the range as the output for the formula. I wanted to put this in a Function procedure so that I can call it using named ranges then put that directly into the series formula for the chart.

Current Output: #Value! and a reference error when the sheet recalculates.

What I think the Problem is: I dont think I am the input values are in the argument list are correct.

Please, I'm USA on this thing. (out of gas haha) Any help or suggestions please post.

Code:
Function dynChart(P As Double, Z As Double, Off As Double, Rng As Range) As String

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Created: 03/07/2011 7:50a '
'Purpose: to create a function that will allow the use of dynamic Rang'
' e easily '
'Created by: Hans Tremmel '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim RowCnt As Double, Max As Double, Min As Double
Dim Ref As Double
Dim Temp As Range
Dim Msg As String

'Error Check: Check to make sure only 1 column in Rng Selected
'Error Check: that all values are accounted for
'Rng.Activate

'03/07 Tried to go with array way of do things. Reference 293 and try to pick it up tomorrow.
RowCnt = UBound(Rng())
x = Round(P / 100 * RowCnt, 0)
y = Round(Z / 100 * RowCnt, 0)
If x - y / 2 <= 0 Then
Min = 1
Max = y - x + 1
Else
If x + y / 2 >= RowCnt Then
Max = RowCnt
Min = RowCnt - y
Else
Min = x - y / 2
Max = x + y / 2
End If
End If

Min = Round(Min, 0)
Max = Round(Max, 0)
Worksheets(Rng.Worksheet.Name).Activate

Set Temp = Rng.Range(Cells(Min, 1), Cells(Max, 1)).Offset(0, Off)
Msg = Temp.Address
Msg = Temp.Worksheet.Name & "!" & Msg
dynChart = Msg

End Function