Need help on With statement
 

I am working on a function that was provided to me by xx. (My Outlook
express won't show the message so I cannot attribute this to the correct
person). My goal is to make this more general by extract sections out to
subroutines. The critical sections a
'
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
and how to modify statements 6 through 9 so they can operator 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 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?


Bryan Kelly
Time is the medium we use to express out priorities.

Need help on With statement
 

You might as well do all the work in the function.

Dim sh as Worksheet
Dim ET_Column as Range, EL_Column as Range
Dim EL_cmd_Column as Range
Dim EL_auto_Column as Range
Dim rng as Range
set sh = worksheets("Data")
set ET_Column = Select_Column_By_Name("Elapsed Time", sh)
set EL_Column = Select_Column_By_Name("EL", sh)
set EL_cmd_Column = Select_Column_By_Name("EL cmd", sh)
set EL_auto_Column = Select_Column_By_Name("EL auto", sh)
'

Set rng = Union(ET_Column, EL_Column, _
EL_cmd_Column, EL_auto_Column)
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns
End Sub


Function Select_Column_By_Name(find_target As String, _
sh as Worksheet) as Range
Dim caller_range as Range
Set caller_range = sh.Range("A1:AZ1"). _
Find(find_target, _
Lookat:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)
set caller_range = sh.Range(caller_range,caller_range.End(xldown))
set Select_Column_By_Name = caller_range
End Function

--
Regards,
Tom Ogilvy
"Bryan Kelly" wrote in message
. ..
I am working on a function that was provided to me by xx. (My Outlook
express won't show the message so I cannot attribute this to the correct
person). My goal is to make this more general by extract sections out to
subroutines. The critical sections a
'
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
and how to modify statements 6 through 9 so they can operator 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 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?


Bryan Kelly
Time is the medium we use to express out priorities.