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