Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Problem in Macro
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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. " wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Problem in Macro
On Apr 3, 1:11 pm, wrote:
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Macro | Excel Discussion (Misc queries) | |||
Problem in macro | Excel Discussion (Misc queries) | |||
Macro problem | Excel Worksheet Functions | |||
Macro Problem | Excel Worksheet Functions | |||
Macro problem | New Users to Excel |