VBA - Using variables within formulas

[DKgirlie]

Hi everyone,

I think i have a simple question - i just can't figure it out

Ok i have a macro that just multiplies the value of a cell by a value inputed by the user - so it looks something like this :

Sub Calculate ()
Dim IntEscalationRate As Integer
IntEscalationRate = InputBox("Enter an escalation rate:")

ActiveCell.Select
ActiveCell.FormulaR1C1 = "=RC[-1]* IntEscalationRate"
End Sub

When i run this macro it comes up with a #NAME? error - i am sure this means it is not recognizing the variable - Am i refernecing the variable correctly within the formula?

Thanks heaps - You guys always help me out and i really appreciate it

DEB

 

[DKgirlie]

OK i think i got it - this seems to work

ActiveCell.FormulaR1C1 = "=RC[-1]*1." & IntEscalationRate

 

[Norie]

Perhaps replacing this:

ActiveCell.FormulaR1C1 = "=RC[-1]* IntEscalationRate"

with

ActiveCell.FormulaR1C1 = "=RC[-1]" & "*" & " IntEscalationRate"

 

[DKgirlie]

Cool thank you !

Deb