Jon Peltier question: use of MATCH

[Bryan Kelly]

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?

 

[Norman Jones]

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.

 

[Tom Ogilvy]

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

 

[Bryan Kelly]

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,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:$65536,Xrows,ET))

Name: TheAZ
Refers To: =(INDEX(Sheet1!$1:$65536,2,AZ):INDEX(Sheet1!$1:$65536,Xrows,AZ))

Name: TheEL
Refers To: =(INDEX(Sheet1!$1:$65536,2,EL):INDEX(Sheet1!$1:$65536,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]

Hi Byan,

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



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.

 

[Jon Peltier]

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/
_______

 

[Tom Ogilvy]

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.

 

[Bryan Kelly]

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

 

[Norman Jones]

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.

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]

 

[Bryan Kelly]

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

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.

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]

 

[Tom Ogilvy]

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

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

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.

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]