VBA. Syntax issue. Looping array

Hi
I'm messing with one of my macros and I'm stuck. Below is simplified example of what I'm trying to achieve.

Example start off code:

Please Login or Register  to view this content.

Example code of what I want to achieve or where I want to go with this (needs fixing as currently the syntax is probably incorrect)

Please Login or Register  to view this content.

Right, let me walk this through now.
I added line For Each xStr In Array("1", "2") to go through section of the code with some substitutions.

So when going through the code with array value "1" then section With Range(LGL_profileSelect_ & xStr & LR) should be read as With Range(LGL_profileSelect_1 & LR)
and when its going through line .SpecialCells(xlCellTypeBlanks).Formula = "=INDEX(LGL_profile_" & xStr & ",MATCH(C[-1],C[-10],0))"
then it should read it as .SpecialCells(xlCellTypeBlanks).Formula = "=INDEX(LGL_profile_1,MATCH(C[-1],C[-10],0))"


So later when going through the code with array value "2" it should read these sections as With Range(LGL_profileSelect_2 & LR)
and .SpecialCells(xlCellTypeBlanks).Formula = "=INDEX(LGL_profile_2,MATCH(C[-1],C[-10],0))" respectively.


I think the the bit that I struggle to get right is With Range(LGL_profileSelect_ & xStr & LR)
Btw. "LGL_profile_1" and "LGL_profile_2" are named ranges.

I have attached a spreadsheet that is for these examples above. This spreadsheet includes before and after sheets. Please note that this is just a simplified example with index/match formula. The real spreadsheet contains more complex formulas.


I would appreciate if someone could have a look and maybe help me to a right track or a solution.

Cheers