Help on "With"
 

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

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

Help on "With"
 

You don't need the with if your ACTIVE sheet is the sheet you want. So,
remove the with & end with and the . before range.

--
Don Guillett
SalesAid Software

"Bryan Kelly" wrote in message
. ..
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 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.
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?

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