Need help on With statement

[Bryan Kelly]

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 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
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.

 

[Tom Ogilvy]

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