Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jon Peltier question: use of MATCH
I am trying to use the match command. From the Excel help file I built this
line of code test_variable = MATCH(39000,B2:B8,1) I added "test_variable =" to what I copied from the Excell file. Excel does not like the colon between the B2 and B8 saying it wants a separator. The line from Jon's original note was Name: ET Refers To: =MATCH("Elapsed Time",Sheet1!$1:$1,0) Excel doesn't like either one. How do I get this match to work? -- Bryan Kelly Time is the medium we use to express out priorities. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jon Peltier question: use of MATCH
Hi Bryan,
test_variable = MATCH(39000,B2:B8,1) Try, test_variable = Application.Match(39000, Range("B2:B8"), 1) In Excel you can refer to the range as B2:B8, but in VBA you need to explicitly use a range object and enclose the A1-style address between quotes. --- Regards, Norman "Bryan Kelly" wrote in message ... I am trying to use the match command. From the Excel help file I built this line of code test_variable = MATCH(39000,B2:B8,1) I added "test_variable =" to what I copied from the Excell file. Excel does not like the colon between the B2 and B8 saying it wants a separator. The line from Jon's original note was Name: ET Refers To: =MATCH("Elapsed Time",Sheet1!$1:$1,0) Excel doesn't like either one. How do I get this match to work? -- Bryan Kelly Time is the medium we use to express out priorities. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jon Peltier question: use of MATCH
John's describing creating a defined name (insert=Name=Define).
If you want to use Match in VBA Dim test_variable as Variant test_variable = Application.Match(39000,Range("B2:B8"),1) if iserror(test_variable) then msgbox "39000 not found" else msgbox "found at cell " & range("B2:B8")(test_variable).Address End if -- Regards, Tom Ogilvy "Bryan Kelly" wrote in message ... I am trying to use the match command. From the Excel help file I built this line of code test_variable = MATCH(39000,B2:B8,1) I added "test_variable =" to what I copied from the Excell file. Excel does not like the colon between the B2 and B8 saying it wants a separator. The line from Jon's original note was Name: ET Refers To: =MATCH("Elapsed Time",Sheet1!$1:$1,0) Excel doesn't like either one. How do I get this match to work? -- Bryan Kelly Time is the medium we use to express out priorities. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jon Peltier question: use of MATCH
That is rather odd. The example came from selecting F1 with the "Match"
selected. The window is titled "Microsoft Excel Visual Basic." It seems that one should assume that the example will work for visual basic. Oh well. But on to the next level. I now have: ET_column = Application.Match("Elapsed Time", Range("A1:Z1"), 0) AZ_column = Application.Match("Elapsed Time", Range("A1:Z1"), 0) EL_column = Application.Match("Elapsed Time", Range("A1:Z1"), 0) Range("ET_column, AZ_column, EL_column").Select My goal is to write a macro that will make a chart based on the names in the top row of the columns. When I record a macro to make a chart, one of the lines is: Range("C:C,D:D,E:E").Select But this MATCH function returns a single digit such as 3. How can I use this to build a chart using a macro? Jon Peltier made a suggestion something more than a week ago about using a Define Name function. I looked up "define" and "name" and it had no such entry. If I use this define name operation, can I have a macro to give to a customer that will work for them? Here is what Jon said. You can do this with dynamic ranges. Assuming your labels are in row 1 of Sheet1, here are some defined names that construct the X and Y values for your chart. Press CTRL+F3 to open the Defined Name dialog, and define these names: Name: ET Refers To: =MATCH("Elapsed Time",Sheet1!$1:$1,0) Name: AZ Refers To: =MATCH("AZ",Sheet1!$1:$1,0) Name: EL Refers To: =MATCH("EL",Sheet1!$1:$1,0) (ET, AZ, and EL are the column numbers where these labels are found.) Name: Xrows Refers To: =COUNT(INDEX(Sheet1!$1:$65536,1,ET):INDEX(Sheet1!$ 1:$65536,65536,ET)) (This is the number of rows used in the Elapsed Time column) Name: TheX Refers To: =(INDEX(Sheet1!$1:$65536,2,ET):INDEX(Sheet1!$1:$65 536,Xrows,ET)) Name: TheAZ Refers To: =(INDEX(Sheet1!$1:$65536,2,AZ):INDEX(Sheet1!$1:$65 536,Xrows,AZ)) Name: TheEL Refers To: =(INDEX(Sheet1!$1:$65536,2,EL):INDEX(Sheet1!$1:$65 536,Xrows,EL)) (These are the data ranges under the labels.) Now use the chart wizard to create a chart. In step 2, click on the Series tab, Select or Add the first series, name it "AZ", and enter =Sheet1!theX in the X Values box and =Sheet1!theAZ in the Y Values box. Add the second series, again enter =Sheet1!theX in the X Values box, and enter =Sheet1!theEL in the Y Values box. No matter which column has the particular labels (and the left to right alignment doesn't matter), the dynamic ranges will find the appropriate data for the chart. "Norman Jones" wrote in message ... Hi Bryan, test_variable = MATCH(39000,B2:B8,1) Try, test_variable = Application.Match(39000, Range("B2:B8"), 1) In Excel you can refer to the range as B2:B8, but in VBA you need to explicitly use a range object and enclose the A1-style address between quotes. --- Regards, Norman "Bryan Kelly" wrote in message ... I am trying to use the match command. From the Excel help file I built this line of code test_variable = MATCH(39000,B2:B8,1) I added "test_variable =" to what I copied from the Excell file. Excel does not like the colon between the B2 and B8 saying it wants a separator. The line from Jon's original note was Name: ET Refers To: =MATCH("Elapsed Time",Sheet1!$1:$1,0) Excel doesn't like either one. How do I get this match to work? -- Bryan Kelly Time is the medium we use to express out priorities. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jon Peltier question: use of MATCH
Hi Byan,
"Bryan Kelly" wrote [snip] That is rather odd. The example came from selecting F1 with the "Match" selected. The window is titled "Microsoft Excel Visual Basic." It seems that one should assume that the example will work for visual basic. Oh well Responding just to this point, I highlighted Match on a worksheet and presses F1. I was taken to the Excel Help page for Match which included the following example (abbreviated by me): In the preceding worksheet: MATCH(39000,B2:B8,1) equals 3 MATCH(38000,B2:B8,0) equals 2 The first of these formulae corresponds exactly to that included in your initial post. In the VBE, if I highlight Match and press F1, I am conducted to the Visual Basic Help page entitled: Using Microsoft Excel Worksheet Functions in Visual Basic. This page includes the following pertinent excerpt: If you use a worksheet function that requires a range reference as an argument, you must specify a Range object. For example, you can use the Match worksheet function to search a range of cells. In a worksheet cell, you would enter a formula such as =MATCH(9,A1:A10,0). However, in a Visual Basic procedure, you would specify a Range object to get the same result. Sub FindFirst() myVar = Application.WorksheetFunction _ .Match(9, Worksheets(1).Range("A1:A10"), 0) MsgBox myVar End Sub End of excerpt However, if on pressing F1 in Excel , the resultant window showed your example and the window was entitled "Microsoft Excel Visual Basic." , then I can understand your bemusement. I would find such an occurrence extraordinary. --- Regards, Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jon Peltier question: use of MATCH
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/ _______ Bryan Kelly wrote: That is rather odd. The example came from selecting F1 with the "Match" selected. The window is titled "Microsoft Excel Visual Basic." It seems that one should assume that the example will work for visual basic. Oh well. But on to the next level. I now have: ET_column = Application.Match("Elapsed Time", Range("A1:Z1"), 0) AZ_column = Application.Match("Elapsed Time", Range("A1:Z1"), 0) EL_column = Application.Match("Elapsed Time", Range("A1:Z1"), 0) Range("ET_column, AZ_column, EL_column").Select My goal is to write a macro that will make a chart based on the names in the top row of the columns. When I record a macro to make a chart, one of the lines is: Range("C:C,D:D,E:E").Select But this MATCH function returns a single digit such as 3. How can I use this to build a chart using a macro? Jon Peltier made a suggestion something more than a week ago about using a Define Name function. I looked up "define" and "name" and it had no such entry. If I use this define name operation, can I have a macro to give to a customer that will work for them? Here is what Jon said. You can do this with dynamic ranges. Assuming your labels are in row 1 of Sheet1, here are some defined names that construct the X and Y values for your chart. Press CTRL+F3 to open the Defined Name dialog, and define these names: Name: ET Refers To: =MATCH("Elapsed Time",Sheet1!$1:$1,0) Name: AZ Refers To: =MATCH("AZ",Sheet1!$1:$1,0) Name: EL Refers To: =MATCH("EL",Sheet1!$1:$1,0) (ET, AZ, and EL are the column numbers where these labels are found.) Name: Xrows Refers To: =COUNT(INDEX(Sheet1!$1:$65536,1,ET):INDEX(Sheet1!$ 1:$65536,65536,ET)) (This is the number of rows used in the Elapsed Time column) Name: TheX Refers To: =(INDEX(Sheet1!$1:$65536,2,ET):INDEX(Sheet1!$1:$65 536,Xrows,ET)) Name: TheAZ Refers To: =(INDEX(Sheet1!$1:$65536,2,AZ):INDEX(Sheet1!$1:$65 536,Xrows,AZ)) Name: TheEL Refers To: =(INDEX(Sheet1!$1:$65536,2,EL):INDEX(Sheet1!$1:$65 536,Xrows,EL)) (These are the data ranges under the labels.) Now use the chart wizard to create a chart. In step 2, click on the Series tab, Select or Add the first series, name it "AZ", and enter =Sheet1!theX in the X Values box and =Sheet1!theAZ in the Y Values box. Add the second series, again enter =Sheet1!theX in the X Values box, and enter =Sheet1!theEL in the Y Values box. No matter which column has the particular labels (and the left to right alignment doesn't matter), the dynamic ranges will find the appropriate data for the chart. "Norman Jones" wrote in message ... Hi Bryan, test_variable = MATCH(39000,B2:B8,1) Try, test_variable = Application.Match(39000, Range("B2:B8"), 1) In Excel you can refer to the range as B2:B8, but in VBA you need to explicitly use a range object and enclose the A1-style address between quotes. --- Regards, Norman "Bryan Kelly" wrote in message . .. I am trying to use the match command. From the Excel help file I built this line of code test_variable = MATCH(39000,B2:B8,1) I added "test_variable =" to what I copied from the Excell file. Excel does not like the colon between the B2 and B8 saying it wants a separator. The line from Jon's original note was Name: ET Refers To: =MATCH("Elapsed Time",Sheet1!$1:$1,0) Excel doesn't like either one. How do I get this match to work? -- Bryan Kelly Time is the medium we use to express out priorities. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jon Peltier question: use of MATCH
John laid it out for you.
What you are missing is Insert=Name=Define. This doesn't involve a macro. It is done in Excel itself. -- Regards, Tom Ogilvy "Bryan Kelly" wrote in message .. . That is rather odd. The example came from selecting F1 with the "Match" selected. The window is titled "Microsoft Excel Visual Basic." It seems that one should assume that the example will work for visual basic. Oh well. But on to the next level. I now have: ET_column = Application.Match("Elapsed Time", Range("A1:Z1"), 0) AZ_column = Application.Match("Elapsed Time", Range("A1:Z1"), 0) EL_column = Application.Match("Elapsed Time", Range("A1:Z1"), 0) Range("ET_column, AZ_column, EL_column").Select My goal is to write a macro that will make a chart based on the names in the top row of the columns. When I record a macro to make a chart, one of the lines is: Range("C:C,D:D,E:E").Select But this MATCH function returns a single digit such as 3. How can I use this to build a chart using a macro? Jon Peltier made a suggestion something more than a week ago about using a Define Name function. I looked up "define" and "name" and it had no such entry. If I use this define name operation, can I have a macro to give to a customer that will work for them? Here is what Jon said. You can do this with dynamic ranges. Assuming your labels are in row 1 of Sheet1, here are some defined names that construct the X and Y values for your chart. Press CTRL+F3 to open the Defined Name dialog, and define these names: Name: ET Refers To: =MATCH("Elapsed Time",Sheet1!$1:$1,0) Name: AZ Refers To: =MATCH("AZ",Sheet1!$1:$1,0) Name: EL Refers To: =MATCH("EL",Sheet1!$1:$1,0) (ET, AZ, and EL are the column numbers where these labels are found.) Name: Xrows Refers To: =COUNT(INDEX(Sheet1!$1:$65536,1,ET):INDEX(Sheet1!$ 1:$65536,65536,ET)) (This is the number of rows used in the Elapsed Time column) Name: TheX Refers To: =(INDEX(Sheet1!$1:$65536,2,ET):INDEX(Sheet1!$1:$65 536,Xrows,ET)) Name: TheAZ Refers To: =(INDEX(Sheet1!$1:$65536,2,AZ):INDEX(Sheet1!$1:$65 536,Xrows,AZ)) Name: TheEL Refers To: =(INDEX(Sheet1!$1:$65536,2,EL):INDEX(Sheet1!$1:$65 536,Xrows,EL)) (These are the data ranges under the labels.) Now use the chart wizard to create a chart. In step 2, click on the Series tab, Select or Add the first series, name it "AZ", and enter =Sheet1!theX in the X Values box and =Sheet1!theAZ in the Y Values box. Add the second series, again enter =Sheet1!theX in the X Values box, and enter =Sheet1!theEL in the Y Values box. No matter which column has the particular labels (and the left to right alignment doesn't matter), the dynamic ranges will find the appropriate data for the chart. "Norman Jones" wrote in message ... Hi Bryan, test_variable = MATCH(39000,B2:B8,1) Try, test_variable = Application.Match(39000, Range("B2:B8"), 1) In Excel you can refer to the range as B2:B8, but in VBA you need to explicitly use a range object and enclose the A1-style address between quotes. --- Regards, Norman "Bryan Kelly" wrote in message ... I am trying to use the match command. From the Excel help file I built this line of code test_variable = MATCH(39000,B2:B8,1) I added "test_variable =" to what I copied from the Excell file. Excel does not like the colon between the B2 and B8 saying it wants a separator. The line from Jon's original note was Name: ET Refers To: =MATCH("Elapsed Time",Sheet1!$1:$1,0) Excel doesn't like either one. How do I get this match to work? -- Bryan Kelly Time is the medium we use to express out priorities. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jon Peltier question: use of MATCH
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. So to clarify my original question: I need to write a macro that will find a column by name and create a plot. The first few lines of the macro might look something like: ET_column = Application.Match("Elapsed Time", Range("A1:Z1"), 0) AZ_column = Application.Match("Elapsed Time", Range("A1:Z1"), 0) EL_column = Application.Match("Elapsed Time", Range("A1:Z1"), 0) I want the ET column to be on the X axis and the other two on the Y axis. How can I write a macro to plot columns by column name rather than column position? Bryan "Bryan Kelly" wrote in message ... I am trying to use the match command. From the Excel help file I built this line of code test_variable = MATCH(39000,B2:B8,1) I added "test_variable =" to what I copied from the Excell file. Excel does not like the colon between the B2 and B8 saying it wants a separator. The line from Jon's original note was Name: ET Refers To: =MATCH("Elapsed Time",Sheet1!$1:$1,0) Excel doesn't like either one. How do I get this match to work? -- Bryan Kelly Time is the medium we use to express out priorities. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jon Peltier question: use of MATCH
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] |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jon Peltier question: use of MATCH
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] |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Jon Peltier question: use of MATCH
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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic swap of x- and y - axis (for John Peltier) | Charts and Charting in Excel | |||
For Jon Peltier | Charts and Charting in Excel | |||
Attn John Peltier | Charts and Charting in Excel | |||
Jon Peltier Speedometer Chart | Excel Discussion (Misc queries) | |||
Jon Peltier - Pivot Table Result for yesterday's "Complex Chart" Question | Charts and Charting in Excel |