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