Formula Problem in Macro
I am writing a macro that is generating a formula. This formula, once
the macro runs, is changing in appearance. To better explain, here is
an example:
The macro builds the formula like this:
"=INDEX(Sheet2!B:B,MATCH(Sheet1!D" & i & ",Sheet2!A:A,0))"
where "i" is set in a loop.
I am expecting the spreadsheet to reflect this:
=INDEX(Sheet2!B:B,MATCH(Sheet1!D2,Sheet2!A:A,0))
but I am getting this:
=INDEX(Sheet2!B:B,MATCH(Sheet1!'D2',Sheet2!A:A,0))
Excel is putting tick marks around the cell reference and is causing
the formula to fail. The expected formula does work because I can
manually take the tick marks out and get the result I am looking for.
Can some help me in building the formula so the tick marks are not
produced? Thanks.
DJ
|