To have DIR function accept a variable

So I have a code the lists down file names in folders and the following is the part of the code that responsible to fetch the name of the files

Please Login or Register  to view this content.

This code go through, as it's written, MyFileName = Dir(Key & "*17519*"), the code will fetch all files that has 17519 as part of the name. and it works just fine.

I need to modify the code as per the following to make criteria of a specific text string in the file name be variable depending on the a value entered in a specific cell. so I did the following

Please Login or Register  to view this content.

But it didn't work, how can I have a variable be part of DIR function.

below is the full code just for reference.

Please Login or Register  to view this content.

If you need the W6 value to be part of the file name, you need to either put wildcards in that cell or use:

Please Login or Register  to view this content.