Match and Range Reference Syntax

  • Thread starter Thread starter u473
  • Start date Start date
U

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.
 
Try this:

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

Regards,
Per
 
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)
 
Back
Top