CODEWITHSUNDEEP
excelvba
Sherbetdab
Sherbetdab

Reputation: 127

Index Match Type Mismatch

I'm trying to enter the following formula to a range of cells within a macro

=INDEX('[Rest Days.xlsx]Sheet1'!$B$3:$B$26,MATCH("*"&A6&"*",'[Rest Days.xlsx]Sheet1'!$E$3:$E$26,0))

I keep getting a type mismatch error, I think it's something to do with the "" around the &A6" but can't quite figure it out, Can anyone help me please?

Upvotes: 1

Views: 144

Answers (1)

Sherbetdab
Sherbetdab

Reputation: 127

I've managed to get it working. 

Range("G5:G14").Formula = "=INDEX(Nights!$B$2:$B$23,MATCH("" * ""&A5&"" * "",Nights!$E$2:$E$24,0))"

was suggested but was returning a #NA error.

The #NA was due to the space either side of the * within the quotes.

Range("G5:G14").Formula = "=INDEX(Nights!$B$2:$B$23,MATCH(""*""&A5&""*"",Nights!$E$2:$E$24,0))"

was the line of code that worked for me.

Thank You.

Upvotes: 1

Related Questions