Creating an "INDEX" formula in a cell using a macro

[bhujanga]

I need to create a slew of 'INDEX' formulas that access arrays in many varied locations. I use code in my macro to figure out where the particular array is located and then I want it to write the appropriate 'INDEX' formula in the cell. Everything is working except that it is adding single quotes within the formula that cause it to be unrecognizable. Specifically this is an example:

The string that my macro builds looks like this:
=INDEX(Adams!E3:J7,1,1)

I store it in a variable called "Formula", then execute the following statement:
ActiveCell.Offset(0, z).FormulaR1C1 = Formula

What appears in the cell looks like this:
=INDEX(Adams!'E3':'J7',1,1)
Where are these quotes coming from and how do I get around this problem?

(I've used message boxes to confirm that the string doesn't initially contain these quotes.)

 

[SkipVought]

Hi,

Do you want us to GUESS what code your Formula function uses?

Or would you care to submit exactly what code you are using in order that you might improver your probablility of receiving a more cogent and focused reply?

FYI, VBA questions are best addressed in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bhujanga]

As I said in the post:

The string that my macro builds looks like this:
=INDEX(Adams!E3:J7,1,1)

I store it in a variable called "Formula" ...

 

[bhujanga]

I've figured this one out.

I'd like to say that I believe this forum is great and that I appreciate the help I get here immensely. In this case, however I think Skip's reply was extremely rude and uncalled for.

 

[GlennUK]

I think you gave enough information to describe your problem ... I guess you know that the FormulaR1C1 property wasn't the one to use, now. Er, I don't want to say anything against Skip, as he's always seemed OK to me ... maybe it was a bad day or something ( no offence Skip )

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

The OP was referring to a MACRO named 'Formula'. Although the OP posted the intended formula result, he NEVER posted the code in his 'Formula' macro. How is anyone supposed to provide a solution, not knowing the problem(s) in the VBA macro code in the 'Formula' macro?

Now, I am not sure if the OP indeed meant a function or a variable, referring to his 'Formula'.

For the benefit of all other Tek-Tip members, please post your solution so that others may benefit from your discovery.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bhujanga]

The solution was simply that I was using the 'FormulaR1C1' property when I should have just been using the 'Formula' property. When you use the FormulaR1C1 property it requires that cell references be relative. Therefore it apparently got confused by the literal cell references in the string that the macro created. Using the 'Formula' property instead puts the string in there exactly as it is written, which was what I wanted.

Skip,
I didn't say there was a macro named 'Formula'. I believe my description of the problem was clear (it was enough for Glenn to understand.) In any case, even had it not been, my issue is that your request for additional information was profuse with sarcasm...most unbecoming.
Bhu

 

[SkipVought]

but there WAS a macro, regardless of the name.

Where's the macro that produced the formula?

I appologize for the excessive sarcasm, but I so often see questions referring to information that is withheld and must be requested.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}