ActiveCell.FormulaR1C1 Put's Single Quotes Around Cell Reference

[Billy Hill]

I'm trying to get a formula into a cell from a macro and I'm getting a single quote isolating what should be a cell reference.

The current formula works fine, it looks like this:
=0.85+(75/D2290)+(0.3*0)

The formula I'm trying to make should come out like this, dividing D2290 by Cell K1:
=0.85+(75/(D2290*K1))+(0.3*0)

But it comes out like this instead with single quotes around the K1:
=0.85+(75/(D2290*'K1'))+(0.3*0)

This gives me a "#NAME" error.

Here is the code to create the formula that works:
ActiveCell.FormulaR1C1 = "=" & M & "+(" & P & "/RC[-3])+(.3*" & H & ")"

And here is the code that I thought would work but doesn't:
ActiveCell.FormulaR1C1 = "=" & M & "+(" & P & "/(RC[-3]*K1))+(.3*" & H & ")"

How do I get rid of the single quotes around the K1?

Thanks in advance!

 

[Billy Hill]

I realized I need the cell referenced like $K$1 so I changed the forumla to

ActiveCell.FormulaR1C1 = "=" & M & "+(" & P & "/(RC[-3]*$K$1))+(.3*" & H & ")"

Now I get an error saying "Application defined or object-defined error.

Ack!

 

[Marcelo Branco]

Your formula is mixing RC and A1 references

Maybe this using A1 reference-style

ActiveCell.Formula = "=0.85+(75/(D2290*K1))+(0.3*0)"

Adjust ActiveCell to your target cell/range

M.
ps: i dont understand this part: +(0.3*0)

 

[Norie]

You need to change K1 to it's R1C1 equivalent.

 

[Billy Hill]

Your formula is mixing RC and A1 references

Maybe this using A1 reference-style

ActiveCell.Formula = "=0.85+(75/(D2290*K1))+(0.3*0)"

Adjust ActiveCell to your target cell/range

M.
ps: i dont understand this part: +(0.3*0)

The formula is built with variables so I create it with code. I used the recorder to get the basics and modified it from there, hence using the R1C1 method. Is there a better way?

What the code does is calculate a material and plating cost. I use 3 input boxes to get the material cost, the plating cost and the number of plugged holes.

The +(0.3*0) is used if there are holes to be plugged before plating. They cost $.30 each to plug.

 

[Billy Hill]

You need to change K1 to it's R1C1 equivalent.

Doh!

That's right, I'm not using a range.

Thanks!

Hey, it put the $'s in there for me! Sweet!

 

[Norie]

Glad it worked.

I was going to post what it might have been but if it wasn't an absolute reference then the R1C1 could depend on where the formula was going.