Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with With statement
Outlook crashed while sending this message and I did not see it posted. My
appologies if this is a double post. 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with 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 m... Outlook crashed while sending this message and I did not see it posted. My appologies if this is a double post. 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with With statement
Hello Tom,
I had some computer problems and wound up completly formatting my hard drive and re-installing everything. Now I can see my messages and your reply. I will be putting you suggestions into action tomorrow. Thanks for your time, Bryan "Tom Ogilvy" wrote in message ... 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 m... Outlook crashed while sending this message and I did not see it posted. My appologies if this is a double post. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions |