B
Bryan Kelly
I have attempted to post this message twice but I cannot see it in the news
group.
I see many messages posted today, but not mine, posted some five hours ago.
If this is a repeat, please send me private mail and I will try to discover
the problem.
My private address is re at bkelly dot ws
Thank you.
am working on a function that was provided to me by someone in this
newsgroup. (My Outlook express won't show the message so I cannot
attribute this to the correct person.
My goal is to make this function more general by extracting sections out to
subroutines. The critical sections are:
'
1 Call Select_Column_By_Name("Elapsed Time", ET_Column)
2 Call Select_Column_By_Name("EL", EL_Column)
3 Call Select_Column_By_Name("EL cmd", EL_cmd_Column)
4 Call Select_Column_By_Name("EL auto", EL_auto_Column)
'
5 With Worksheets("data")
6 Set ET_Column = .Range(ET_Column, ET_Column.End(xlDown))
7 Set EL_Column = .Range(EL_Column, EL_Column.End(xlDown))
8 Set EL_cmd_Column = .Range(EL_cmd_Column, EL_cmd_Column.End(xlDown))
9 Set EL_auto_Column = .Range(EL_auto_Column, EL_auto_Column.End(xlDown))
10 End With
11 Set rng = Union(ET_Column, EL_Column, _
12 EL_cmd_Column, EL_auto_Column)
13 Charts.Add
14 ActiveChart.ChartType = xlXYScatterLinesNoMarkers
15 ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns
16 End Sub
17 Sub Select_Column_By_Name(find_target As String, caller_range As Range)
18 Set caller_range = ActiveSheet.Range("A1:AZ1"). _
19 Find(find_target, _
20 Lookat:=xlWhole, _
21 LookIn:=xlValues, _
22 MatchCase:=False)
23 End Sub
To the best of my understanding, lines 1 through 4 create ranges consisting
of the header cell of a column. Lines 5 through 10 extend the range down to
the end of the column.
My problem is that I do not understand why I need the With of statement 5.
If statements 1 through 4 don't need to specify the work sheet, they why do
lines 6 through 9 need the worksheet specified?
How to modify statements 6 through 9 so they can operate outside the
With umbrella. To clarify, I want to move line 6 from the with statement
into the subfunction of lines 17 through 23. The end result of this will be
to have a function that 1) picks a column by header name, 2) assigns that
column to a range, and 3) extends the range down to the last row of the
column.
If I just move line 6 down into the function and edit it approriately:
Set caller_range = .Range( caller_range, caller_range.End(xlDown))
Excel does not like the statement complaining that .Range is an invalid or
unqualified reference. I have attempted to alter to statement in several
ways to resolve the problem to no avail.
Using F1 on With just doesn't provide the information I need.
How can I make the change?
group.
I see many messages posted today, but not mine, posted some five hours ago.
If this is a repeat, please send me private mail and I will try to discover
the problem.
My private address is re at bkelly dot ws
Thank you.
am working on a function that was provided to me by someone in this
newsgroup. (My Outlook express won't show the message so I cannot
attribute this to the correct person.
My goal is to make this function more general by extracting sections out to
subroutines. The critical sections are:
'
1 Call Select_Column_By_Name("Elapsed Time", ET_Column)
2 Call Select_Column_By_Name("EL", EL_Column)
3 Call Select_Column_By_Name("EL cmd", EL_cmd_Column)
4 Call Select_Column_By_Name("EL auto", EL_auto_Column)
'
5 With Worksheets("data")
6 Set ET_Column = .Range(ET_Column, ET_Column.End(xlDown))
7 Set EL_Column = .Range(EL_Column, EL_Column.End(xlDown))
8 Set EL_cmd_Column = .Range(EL_cmd_Column, EL_cmd_Column.End(xlDown))
9 Set EL_auto_Column = .Range(EL_auto_Column, EL_auto_Column.End(xlDown))
10 End With
11 Set rng = Union(ET_Column, EL_Column, _
12 EL_cmd_Column, EL_auto_Column)
13 Charts.Add
14 ActiveChart.ChartType = xlXYScatterLinesNoMarkers
15 ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns
16 End Sub
17 Sub Select_Column_By_Name(find_target As String, caller_range As Range)
18 Set caller_range = ActiveSheet.Range("A1:AZ1"). _
19 Find(find_target, _
20 Lookat:=xlWhole, _
21 LookIn:=xlValues, _
22 MatchCase:=False)
23 End Sub
To the best of my understanding, lines 1 through 4 create ranges consisting
of the header cell of a column. Lines 5 through 10 extend the range down to
the end of the column.
My problem is that I do not understand why I need the With of statement 5.
If statements 1 through 4 don't need to specify the work sheet, they why do
lines 6 through 9 need the worksheet specified?
How to modify statements 6 through 9 so they can operate outside the
With umbrella. To clarify, I want to move line 6 from the with statement
into the subfunction of lines 17 through 23. The end result of this will be
to have a function that 1) picks a column by header name, 2) assigns that
column to a range, and 3) extends the range down to the last row of the
column.
If I just move line 6 down into the function and edit it approriately:
Set caller_range = .Range( caller_range, caller_range.End(xlDown))
Excel does not like the statement complaining that .Range is an invalid or
unqualified reference. I have attempted to alter to statement in several
ways to resolve the problem to no avail.
Using F1 on With just doesn't provide the information I need.
How can I make the change?