I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named H2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks

Melric writing again, I errored in my original post. I named J2 as
"picktime1".......


I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named J2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks


"melric" wrote:

I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named H2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks

Sub Test()
MsgBox (Application.Match(Range("A3493").Value, _
Range("J1", Range("J1").End(xlDown)), 0))
End Sub

"melric" wrote:

Melric writing again, I errored in my original post. I named J2 as
"picktime1".......


I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named J2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks


"melric" wrote:

I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named H2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks

Thanks so much, it works but needs one tweak; the macro gives me a message
box for the answer, I want to have it put the number in the cell.

Can you help me with that tweak?

"JMB" wrote:


Sub Test()
MsgBox (Application.Match(Range("A3493").Value, _
Range("J1", Range("J1").End(xlDown)), 0))
End Sub

"melric" wrote:

Melric writing again, I errored in my original post. I named J2 as
"picktime1".......


I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named J2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks


"melric" wrote:

I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named H2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks

Oh, I see, you want to put the match function in a specific cell. If you
wanted it to appear in cell A1 (change the cell reference to whatever you
want) then

Sub Test()
Range("A1").Formula = "=Match(A3493, $J$1:" & _
Range("J1").End(xlDown).Address & ", 0)"
End Sub

Basically, you have to concatenate the formula string. Anything that refers
to a VBA variable or object or statement has to be outside quotes (such as
Range("J1").End(xlDown).Address) so that VBA does not treat it as text and
actually processes the statement.

You can, of course use your object variables picktime1 and picktime2.

Sub Test()
Dim picktime1 As Range
Dim picktime2 As Range

Set picktime1 = Range("J1")
Set picktime2 = Range("J1").End(xlDown)

Range("A1").Formula = "=Match(A3493, " & _
picktime1.Address & ":" & picktime2.Address & _
", 0)"
End Sub


One other note, it is often not necessary to select objects in Excel in
order to work with them.

"melric" wrote:

Thanks so much, it works but needs one tweak; the macro gives me a message
box for the answer, I want to have it put the number in the cell.

Can you help me with that tweak?

"JMB" wrote:


Sub Test()
MsgBox (Application.Match(Range("A3493").Value, _
Range("J1", Range("J1").End(xlDown)), 0))
End Sub

"melric" wrote:

Melric writing again, I errored in my original post. I named J2 as
"picktime1".......


I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named J2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks


"melric" wrote:

I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named H2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks

Thank you JMB, I'm a fledging programer at best.
Would you answer one other question please?

Instead of calling out a specific cell (in this case "A3493"), I'd like to
reference the cell two columns to the left.

I tried replacing
ActiveCell.FormulaR1C1 = (Application.Match(Range("A3493").Value, _
Range("h1", Range("h1").End(xlDown)), 0))

with
ActiveCell.FormulaR1C1 = (Application.Match(Range(rc[-2]).Value, _
Range("h1", Range("h1").End(xlDown)), 0))

but it didn't work.

Any other advice?
Thanks again.

"JMB" wrote:

Oh, I see, you want to put the match function in a specific cell. If you
wanted it to appear in cell A1 (change the cell reference to whatever you
want) then

Sub Test()
Range("A1").Formula = "=Match(A3493, $J$1:" & _
Range("J1").End(xlDown).Address & ", 0)"
End Sub

Basically, you have to concatenate the formula string. Anything that refers
to a VBA variable or object or statement has to be outside quotes (such as
Range("J1").End(xlDown).Address) so that VBA does not treat it as text and
actually processes the statement.

You can, of course use your object variables picktime1 and picktime2.

Sub Test()
Dim picktime1 As Range
Dim picktime2 As Range

Set picktime1 = Range("J1")
Set picktime2 = Range("J1").End(xlDown)

Range("A1").Formula = "=Match(A3493, " & _
picktime1.Address & ":" & picktime2.Address & _
", 0)"
End Sub


One other note, it is often not necessary to select objects in Excel in
order to work with them.

"melric" wrote:

Thanks so much, it works but needs one tweak; the macro gives me a message
box for the answer, I want to have it put the number in the cell.

Can you help me with that tweak?

"JMB" wrote:


Sub Test()
MsgBox (Application.Match(Range("A3493").Value, _
Range("J1", Range("J1").End(xlDown)), 0))
End Sub

"melric" wrote:

Melric writing again, I errored in my original post. I named J2 as
"picktime1".......


I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named J2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks


"melric" wrote:

I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named H2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks

Another possible solution (non-vba) is to use a dynamic named range. Click
Insert, names, define and type in a name for your named range. In the refers
to box paste or type in (you may have to hit F2 after clicking in the refers
to box to enter edit mode).

Assuming your sheet name is Sheet1 (change if necessary)

=OFFSET(Sheet1!$J$1,0,0,MATCH(TRUE,ISBLANK(Sheet1! $J$1:$J$65535),0)-1,1)

Then your match formula becomes

=MATCH(A3493, MyNamedRange, 0)


As you add data to column J, your named range will re-define itself (J1 is
the beginning cell (I'm assuming there is data in this cell), the ending cell
is the first blank cell). You can test your named range reference with the
following VBA function

Function RangeAddress(rng As Range) As String
RangeAddress = rng.Address
End Function

Then, in an empty cell type

=rangeaddress(mynamedrange)

and it will return the address of your named range so that you can make sure
you've entered everything properly.


"melric" wrote:

Thanks so much, it works but needs one tweak; the macro gives me a message
box for the answer, I want to have it put the number in the cell.

Can you help me with that tweak?

"JMB" wrote:


Sub Test()
MsgBox (Application.Match(Range("A3493").Value, _
Range("J1", Range("J1").End(xlDown)), 0))
End Sub

"melric" wrote:

Melric writing again, I errored in my original post. I named J2 as
"picktime1".......


I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named J2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks


"melric" wrote:

I would like to define the first and last cells of a column and tell my macro
to give me the first row that a certain value is in.

The value I'm looking to match is in cell A3493, the range I am targeting to
do the Match function is in J2 through the end of that column. I named H2 as
"picktime1". I told the macro to end x1down and named the bottom cell
"picktime2":
Dim picktime1 As Range
Dim picktime2 As Range
Range("j1").Select
Set picktime1 = ActiveCell
Selection.End(xlDown).Select
Set picktime2 = ActiveCell

I want to use range names because the length of the column I am matching to
will vary as I collect each day's data.

I tried the following, but get the "#NAME?" error message:
ActiveCell.FormulaR1C1 = "=MATCH(A3493,picktime1:picktime2,0)"

Any advice?
Thanks