Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help on "With"
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |