this worked for me:
Sub aa_chart_test_2()
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 ET_Column = ActiveSheet.Range("A1:Z1"). _
Find("Elapsed Time", Lookat:=xlWhole)
Set EL_Column = ActiveSheet.Range("A1:Z1"). _
Find("EL", Lookat:=xlWhole)
Set EL_cmd_Column = ActiveSheet.Range("A1:Z1"). _
Find("ELcmd", Lookat:=xlWhole)
Set EL_auto_Column = ActiveSheet.Range("A1:Z1"). _
Find("ELauto", Lookat:=xlWhole)
With Worksheets("Data")
Set ET_Column = .Range(ET_Column, _
ET_Column.End(xlDown))
Set EL_Column = .Range(EL_Column, _
EL_Column.End(xlDown))
Set EL_cmd_Column = .Range(EL_cmd_Column, _
EL_cmd_Column.End(xlDown))
Set EL_auto_Column = .Range(EL_auto_Column, _
EL_auto_Column.End(xlDown))
End With
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
--
Regards,
Tom Ogilvy
"Bryan Kelly" wrote in message
...
Norman,
Thanks for pasting in Jon's reply. I still cannot see it.
After a bunch of work, I will make this real short.
I recorded a macro to make a chart and pared the macro down to the minimum
that
continued to work and give the basic results I want.
Sub aa_chart_test_2()
Set ET_column = ActiveSheet.Range("A1:Z1"). _
Find("Elapsed Time").EntireColumn
Set EL_column = ActiveSheet.Range("A1:Z1"). _
Find("EL").EntireColumn
Set EL_cmd_column = ActiveSheet.Range("A1:Z1"). _
Find("ELcmd").EntireColumn
Set EL_auto_column = ActiveSheet.Range("A1:Z1"). _
Find("ELauto").EntireColumn
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets("data").Range( _
"C1:C3602,E1:E3602,G1:G3602,K1:K3602"), PlotBy:=xlColumns
' ActiveChart.SetSourceData Source:=Sheets("data").Range( _
' ET_column, EL_column, EL_cmd_column, EL_auto_column),
PlotBy:=xlColumns
End Sub
This code works for me. The last line of code, commented out, is the line
I
want to write.
Excel and/or VBA does not like it giving me run time error 450, wrong
number
of arguments
or invalid property type.
I see this line in you post:
Union(ET_column, AZ_column, EL_column).Select
But don't have a clue as to how I might put it to work.
Thank you for your patience,
Bryan
"Norman Jones" wrote in message
...
Hi Bryan.
Jon's latest post gives you a step-by-step detailed VBA approach.
In case it is one of the messages which you were unable to download,
Jon's
post read:
-----------------------------------------
Bryan -
In my earlier post, I was not using VBA at all, but I was creating
defined names within the worksheet. Norman's and Tom's posts showed how
to make the Match function work in VBA.
Match returns the number of the cell within the searched range where the
searched item was found. So ET_column and the others are numbers within
the first row, A1:Z1 to be exact, where the caption was found. As I
said:
(ET, AZ, and EL are the column numbers where these labels are found.)
Incidentally, the way you wrote them, all will be identical numbers
because the searched text is "Elapsed Time" in all three. I assume this
is only in the email.
If you're working in VBA, I'd skip right to range objects:
Dim ET_column as Range
Set ET_column = ActiveSheet.Range("A1:Z1"). _
Find("Elapsed Time").EntireColumn
But this selects the entire column, so we need to define it differently:
' This is the cell right below the label "Elapsed Time":
Set ET_column = ActiveSheet.Range("A1:Z1"). _
Find("Elapsed Time").Offset(1,0)
' This stretches it down to the last contiguous filled cell:
Set ET_column = Range(ET_column, ET_column.End(xlDown))
See, in VBA, I can use a different approach for this. Then with three
columnar ranges defined,
Union(ET_column, AZ_column, EL_column).Select
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
-----------------------------------------------------
---
Regards
Norman.
"Bryan Kelly" wrote in message
.. .
When I first returned to this thread, I found two messages that my
system
(Microsoft Outlook) would not read. When I persisted Outlook
eventually
told
me that they were not longer on the server. One was from Jon and I
don't
know who wrote the other. I no longer see those messages.
Regardless, I see that Jon's answer was not intended to help with a
macro.
[snip]