VBA Match variable range

Question

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?

user3598756 · Accepted Answer · 2017-02-25 17:55:30Z

2

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)

answered Feb 25, 2017 at 17:55

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gasiak A. Over a year ago

however I have a variable range, so is it possible to find another way to set it with cells property, may be?

user3598756 Over a year ago

It is a different issue from the question posted, isn't it? If so then make a new post. In the meanwhile you may want to accept the answer if it solved your original question. Thank you

YowE3K Over a year ago

@GasiakA. - if you have a variable range, change the value of the variable a (or change the hard-coded 5 and 4 to be variables if they are the bits that need to change)

Tim Williams · Accepted Answer · 2017-02-25 19:18:52Z

1

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

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

answered Feb 25, 2017 at 19:18

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

VBA Match variable range

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related