Replace Placeholder Text

I have a spreadsheet that I receive from my customer containing business requirements. One such requirement is a string of text that sometimes contains variable(s) represented by placeholders. I need to copy this string into another worksheet and replace the placeholders with my internal variables. This will allow for a straight comparison during QA.

Worksheet 1
Cell A1
Lorem ipsum [!--$VARBL1_TXT$--] sit amet [!--$VARBL2_TXT$--]

Worksheet 2
Cell A1
Lorem ipsum %%Variable1%% sit amet %%Varaiable2%%

I am currently copying W1/A1 into W2/A1 using this formula: =IF('W1'!A1="","",'W1"!A1)

I do have a table that I can use for a VLOOKUP but the logic to return both the W1/A1 with replaced values has me baffled. I have written this macro but it doesn't work on the cell value returned from the formula in W2/A1:

Please Login or Register  to view this content.

What is the best way to manage this? I considered doing nested substitutes but there are apx 30 variables. Thanks in advance!