Formula Problem in Macro

[therabbittx]

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

 

[Guest]

D2 is a cell reference but it appears you're using it as a sheet name?
That's why the tick marks appear. Test it out on another workbook/formula.

Dave

 

[therabbittx]

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

Is there anyone who can help me with this?