1
y = Application.WorksheetFunction.Match(51187, Sheets("New Master Data 6.1").Range(Cells(1, 1), Cells((Rows.Count), 1)), 0)

I cant find whats wrong with my syntax. Help please :)

  • Close duplicate of [Count the number of rows in another sheet](http://stackoverflow.com/questions/27763089/count-the-number-of-rows-in-another-sheet/27763394#27763394). –  Jun 16 '16 at 00:44

1 Answers1

2

If "New Master Data 6.1" is not the active sheet then your code will error, because in a regular code module any instance of Cells() without a qualifying worksheet will refer to the active sheet.

I would use something like:

With Sheets("New Master Data 6.1")
    y = Application.Match(51187, .Columns(1), 0)
End with

If Not IsError(y) Then
    'do something with y
Else
    'value was not found
End If

Note that dropping the WorksheetFunction allows you to test the return value, instead of triggering a run-time error when there's no match.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Note that y cannot be a Long; it must be a variant to catch the error –  Jun 16 '16 at 01:18