Match and Range Reference Syntax

[u473]

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.

 

[Per Jessen]

Try this:

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

Regards,
Per

 

[Dave Peterson]

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)

 

[u473]

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