Using range names in a macro formula

[Guest]

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,picktime1icktime2,0)"

Any advice?
Thanks

 

[Guest]

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,picktime1icktime2,0)"

Any advice?
Thanks

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

There is no column to the left of cell A3493.

One way to reference a cell relative to another cell is to use Offset(row
offset, col offset). For example

Activecell.Offset(0, -2)

will reference the cell two columns to the left of the active cell.

Range("C2").Offset(0, -2)

would refer to cell A2.


If you want the actual MATCH formula to appear in cell A1 (for example), the
cell two columns to the right of A3493 contains the value you want to look
up, J2 through the end of column J contains the list of values (your lookup
table), then

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


If you are just looking to hardcode the value returned by the Match function
into a cell (say A1) of your workbook then (using the same assumptions)
instead of using the message box you can assign the value to cell A1:


Sub Test2()
Range("A1").Value = Application.Match(Range("A3493").Offset(0, 2).Value, _
Range("J2", Range("J2").End(xlDown)), 0)
End Sub

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.

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.

 

[Guest]

Thank you JMB,

I appoligize, I was not clear on my last question.

The formula is residing in cell "C3493" and references cell "A3493". My
question was how to do a relative reference to "A3493" not actually naming
it. This formula will not always be written on row 3493.

I think you answered my question in your detailed explanation.

There is no column to the left of cell A3493.

One way to reference a cell relative to another cell is to use Offset(row
offset, col offset). For example

Activecell.Offset(0, -2)

will reference the cell two columns to the left of the active cell.

Range("C2").Offset(0, -2)

would refer to cell A2.


If you want the actual MATCH formula to appear in cell A1 (for example), the
cell two columns to the right of A3493 contains the value you want to look
up, J2 through the end of column J contains the list of values (your lookup
table), then

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


If you are just looking to hardcode the value returned by the Match function
into a cell (say A1) of your workbook then (using the same assumptions)
instead of using the message box you can assign the value to cell A1:


Sub Test2()
Range("A1").Value = Application.Match(Range("A3493").Offset(0, 2).Value, _
Range("J2", Range("J2").End(xlDown)), 0)
End Sub

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.

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.

:

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?

:


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

:

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,picktime1icktime2,0)"

Any advice?
Thanks


:

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,picktime1icktime2,0)"

Any advice?
Thanks