Use column header in Range Formula for Vlookup vba

hi I encounter an error that it is invalid in red. How do I correct the column header "Header 2nd Number (Dept Office)"?

I'm using column header as this is a standard template.

Please Login or Register  to view this content.

In addition, how do I apply the same to all files in a folder?

Hope to answer the first qns first.

Thank you!

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however your post does not comply with our guidelines

Please take a moment to review how to add code tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Guideline #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here.

I have added the code tags for you today. Please take a few minutes to re-read our guidelines and rules that you agreed to when you joined and comply in the future