Index Match Type Mismatch

1
votes

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?

1
excelvba
"" is just an empty string. If you want to add an actual quote, then you need to use it four times: """". (start and beginning of text, second instance as escape character and third one is the actual quote character. – Máté Juhász
Please post the code as is, because we cannot know how you inserted the above formula in VBA code, and the problem might be there. – A.S.H
Range("G5:G14").Formula = "=INDEX(Nights!$B$2:$B$23,MATCH("" * ""&A5&"" * "",Nights!$E$2:$E$24,0))" – user3598756
Thanks for the advice, Range("G5:G14").Formula = "=INDEX(Nights!$B$2:$B$23,MATCH("" * ""&A5&"" * "",Nights!$E$2:$E$24,0))" this stops the runtime error but is returning a #NA instead of the result it should. – Sherbetdab
well, that has to do with your data! you may want to make a new post and explain /show the necessary details to have people help you out – user3598756

1 Answers

1
votes

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.