CODEWITHSUNDEEP
vbaexcel
Gasiak A.
Gasiak A.

Reputation: 5

VBA Match variable range

I have a problem with the .Match function. If I set a range inside it with quotes and letters ("A:A") it goes well. But when I try to set it with variable, it gives a 1004 error.

This works:

a = WorksheetFunction.Match(Range("A1"), Sheets("Data").Range("A:A"), 0)

This doesn't work:

a = WorksheetFunction.Match(Range("A2"), Sheets("Data").Range(Cells(a, 4), Cells(a + 5, 4)), 0)

How do I set a range with variables in a .Match function?

Upvotes: 0

Views: 3179

Answers (2)

Tim Williams
Tim Williams

Reputation: 166825

You might find it easier to use Resize() here:

a = WorksheetFunction.Match(Range("A2"), _
                            Sheets("Data").Cells(a, 4).Resize(6, 1), 0)

Upvotes: 1

user3598756
user3598756

Reputation: 29421

you must use fully qualified (up to worksheet object) range references, otherwise they default to currently "active" sheet

  a = WorksheetFunction.Match(Range("A2"), Sheets("Data").Range(Sheets("Data").Cells(a, 4), Sheets("Data").Cells(a + 5, 4)), 0)

Upvotes: 2

Related Questions