MrExcel Publishing
Your One Stop for Excel Tips & Solutions

need help assigning one range to another range


Posted by Edward C. on April 20, 2001 6:18 AM

Hi Everyone,
I am working on a plotting tool for my group who works with large data files ( 100 columns by 2500 rows ). Even though the data files are hugh, the part which actually gets used to create the plot is rather small ( only two or three columns from rows 500 to 1500 ). I have worked out the part that identifies the columns to use in the plot, and now I am working on automating the part that identifies the range of rows to use in creating the plot. I know this is a long question but I tried to orginize it to make it easy for people to go through and give me suggestions. I would appreicate any help you can give me.
Thanks
Edward
The first part of this code, the part that automatically calculates the default outer range of rows to plot over, I have finished and it works
'
' part where I dimension the Ranges used in forms
'
Option Explicit
Dim cSheets As Integer
'Public cChartSheets As Integer
Public sSheetNames(1 To 10) As String
Public sChartSheetNames(1 To 10) As String
Public s100F As Range
Public s100L As Range
Public xUsrF As Range
Public xUsrL As Range

'
' the following is the first part of code
'

Private Sub UserForm_Initialize()
Dim ws As Object 'worksheet
'Dim s100F As Range
'Dim s100L As Range

'ReDim sSheetNames(1 To 10)
lstStep1.Clear
cSheets = 0
For Each ws In ActiveWorkbook.Sheets

cSheets = cSheets + 1

'Redimension arrays if necessary
'If UBound(sSheetNames) < cSheets Then
' ReDim Preserve sSheetNames(1 To cSheets + 5)
'End If

'Save name of sheet
sSheetNames(cSheets) = ws.Name

'Add sheet name to list box
lstStep1.AddItem sSheetNames(cSheets)

Next

Set s100F = Worksheets("Veh1_NY_20010321_1957").Columns("BR"). _
Find(what:="100")
Range(s100F.Address).EntireRow.Select
Application.Intersect(Range("A:A"), Selection).Select
txtStep3min.Text = Selection.Text

'ActiveWorkbook.Sheets("Veh1_NY_20010321_1957").Activate
Range("BR:BR").SpecialCells(xlCellTypeLastCell).Select
Set s100L = Worksheets("Veh1_NY_20010321_1957").Columns("BR"). _
Find(what:="100", SearchDirection:=xlPrevious)
Range(s100L.Address).EntireRow.Select
Application.Intersect(Range("A:A"), Selection).Select
txtStep3max.Text = Selection.Text

End Sub

The next part I am not sure If I did it right or not. I used the "Enter" member under the txtStep3.min and txtStep3.max is where I put the code to change the range values from the default values to the values just typed in by the user. This code is executed when the user hit enter inside the textbox ( at least that is my understanding ). I only showed the min one here, but I do a similar thing for the max one.

'
' code to update min range value input into textbox by user
'

Private Sub txtStep3min_Enter()
'
' This is where the min range input by the user takes effect
'
For i = s100F.Row To s100L.Row
If ActiveSheet.Rows(i).Cells(1).Value > txtStep3min.Value Then
Set xUsrF = ActiveSheet.Rows(i).Cells(1)
Exit Sub
End If
Next i

End Sub
'


The part which allows the user to type in the time values over which to plot in the textbox, and which then has the code step through the rows in the range to find the row with that value and reset the first or last row over which to plot to that row instead of the default outer row, is not working. This code is containing in the click section of the "Plot" command button on my form.
'
' searches for row where user has indicating to start
' stop plot and then sets range to over those values
'

Private Sub cmdPlot_Click()
'
' dimension all variables
'

Dim ARange As Range
Dim HRange As Range
Dim AVRange As Range
Dim CHRange As Range

omitted unrelated code
omitted unrelated code
omitted unrelated code
........


'
' Adding new chart as a separate Chart Sheet
'

' setting min and max for the plot range

' error message '91' received here
' Object Variable or With block variable not set
' Debug points to the line directly below
' "Set ARange = ...."
' Note: I am not sure about the syntax I used for
' reassigning the range to the cell value
' under txtStep3min.Enter ( please see that
' section of code ).
Set ARange = Range("A" & xUsrF.Row, "A" & xUsrL.Row)
Set HRange = Range("H" & xUsrF.Row, "H" & xUsrL.Row)
Set AVRange = Range("AV" & xUsrF.Row, "AV" & xUsrL.Row)
Set CHRange = Range("CH" & xUsrF.Row, "CH" & xUsrL.Row)

'Set ARange = Range("A" & s100F.Row, "A" & s100L.Row)
'Set HRange = Range("H" & s100F.Row, "H" & s100L.Row)
'Set AVRange = Range("AV" & s100F.Row, "AV" & s100L.Row)
'Set CHRange = Range("CH" & s100F.Row, "CH" & s100L.Row)


Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers


ActiveChart.SetSourceData Source:=Sheets("Veh1_NY_20010321_1957").Range( _
ARange.Address & "," & HRange.Address & "," & AVRange.Address & "," & CHRange.Address), PlotBy:=xlColumns


Posted by Dave Hawley on April 20, 2001 6:38 AM

part where I dimension the Ranges used in forms

Hi Edward

Where are you setting you range variant "xUsrL" ?
You have used it to pass it's row number to:

Set ARange = Range("A" & xUsrF.Row, "A" & xUsrL.Row)
Set HRange = Range("H" & xUsrF.Row, "H" & xUsrL.Row)
Set AVRange = Range("AV" & xUsrF.Row, "AV" & xUsrL.Row)
Set CHRange = Range("CH" & xUsrF.Row, "CH" & xUsrL.Row)


But it looks to me that it would be Empty or Nothing.


Dave

OzGrid Business Applications

Posted by Edward C on April 20, 2001 6:44 AM

Hi Dave,
I believe this is the part you are asking about:
thanks
Edward
( copied from middle of last submission ) code to update min range value input into textbox by user This is where the min range input by the user takes effect

------------------------------------------------

part where I dimension the Ranges used in forms

Posted by Dave Hawley on April 20, 2001 7:54 AM

Edward, Ok but when you get your Run Time Error what is the value of "xUsrF.Row" and "xUsrL.Row".


DaveOzGrid Business Applications

Posted by Edward C. on April 20, 2001 8:11 AM

Sorry I misunderstood what you were asking. I checked and when I get the run time error the value of xUsrF.Row = <Object variable or With block varible not set >
and
xUsrL.Row = 2353

Note: even though xUsrL.Row is a number, it is the default max row value. it should be 1866 because i created a test where I reduced the max time to plot over by 10 secs which would have brought me up to row 1866 but my code is ignoring that input.
thanks
edward Edward, Ok but when you get your Run Time Error what is the value of "xUsrF.Row" and "xUsrL.Row".

Posted by Edward C. on April 20, 2001 8:15 AM

(for some reason my reply did not post correctly )
inside the section of code assigned to the plot button,
just after the "set ARange = Range ....
xUsrF.Row is <Object varible or with block variable not set and
xUsrL.Row is 2353

Note:
even though the xUsrL.Row value is the correct default row value for the max row over which a plot can be made, i did a test where i typed into the textbox a time value that was 10 secs above that which should have caused xUsrL.Row to be 1866 but my code seems to be ignoring my input.
thanks
edward

, Ok but when you get your Run Time Error what is the value of "xUsrF.Row" and "xUsrL.Row".

Posted by Edward C. on April 20, 2001 8:19 AM

sorry, the submit form is acting funny, hopefully the third time is the charm:
i checked:
in the code assigned to the plot button,
xUsrF.Row is "Object variable or with block varible not set"
xUsrL.Row is "2353"
Note:
2353 is the default max row value that gets computed inside of the initialize form section, i did a test where i reduced the max time for the plot by 10 secs ( which should have made xUsrL.Row equal 1866 ) but the code is ignoring the value I input in the text box for some reason or if it is seeing it it is not reassigning xUsrL.Row.
thanks
edward

, Ok but when you get your Run Time Error what is the value of "xUsrF.Row" and "xUsrL.Row".