Match and Range Reference Syntax
 

This syntax works fine,
Rng = Application.Match("Grand Total", .Range("A2:A100"), 0)
..
However, I would rather replace the fixed Range("A2:A100") with a
formula that would
incorporate, if acceptable, the Cells(Rows.Count, 1).End(xlUp).Row
Something like :
Rng = Application.Match("Grand Total", .Range("A2:A" &
Cells(Rows.Count, 1)._
End(xlUp).Row), 0)
..
....but my syntax fails.
Help appreciated.
J.P.

Match and Range Reference Syntax
 

Try this:

Rng = Application.Match("Grand Total", .Range("A2", _
.Range("A" & Rows.Count).End(xlUp)), 0)

Regards,
Per

On 19 Okt., 02:51, u473 wrote:
This syntax works fine,
Rng = Application.Match("Grand Total", .Range("A2:A100"), 0)
.
However, I would rather replace the fixed Range("A2:A100") with a
formula that would
incorporate, if acceptable, the Cells(Rows.Count, 1).End(xlUp).Row
Something like :
Rng = Application.Match("Grand Total", .Range("A2:A" &
Cells(Rows.Count, 1)._
* * * * * End(xlUp).Row), 0)
.
...but my syntax fails.
Help appreciated.
J.P.

Match and Range Reference Syntax
 

Make sure you qualify your ranges (including cells() and rows).

Rng = Application.Match("Grand Total", _
.Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row), 0)






On 10/18/2010 19:51, u473 wrote:
This syntax works fine,
Rng = Application.Match("Grand Total", .Range("A2:A100"), 0)
.
However, I would rather replace the fixed Range("A2:A100") with a
formula that would
incorporate, if acceptable, the Cells(Rows.Count, 1).End(xlUp).Row
Something like :
Rng = Application.Match("Grand Total", .Range("A2:A"&
Cells(Rows.Count, 1)._
End(xlUp).Row), 0)
.
...but my syntax fails.
Help appreciated.
J.P.

--
Dave Peterson

Match and Range Reference Syntax
 

Thank you both.
Have a good day,
J.P.