FormulaR1C1 Issue

[MikeDBMan]

If I enter this in VBA code, it works fine:

Cells(X, Y).FormulaR1C1 = "=ROUND(RC[-12]*2.31556463,2)-401.59"

But if I enter this:

Dim TheFormula As String
Let TheFormula="=ROUND(RC[-12]*2.31556463,2)-401.59"
cells(x,y).formular1c1= TheFormula

This time I get the error message "Runtime Error '438': Object does not support this property or method."

How can I solve this as I have to use a variable to build up what the formula-string will be. Thanks you!

 

[Jonmo1]

It works fine for me...

Is that the EXACT code you have, or have you tried to simplify it for our benefit?

 

[MikeDBMan]

I simplified it. Here is the entire code. the cell I am working on O25 has this formula in it currently: =Round(C25*MfgOHPro,2). MfgOHPro is a named range, obviously.
And MfgOHPro = the 2.31556463 also.
I just want to replace the named range with its value but take into account a penny or 2 that might not calculate correctly and subtract or add that extra penny so the value of the cell is the same as it was before I substituted the value for the named range.

Sub ChangeVariableToVal()
Dim Theform As String
Dim TheSum As Long
Dim Tripped1, Tripped2, Tripped3, Tripped4, Tripped5, Tripped6, Tripped7, Tripped8 As Integer
Dim TheAnswer As Variant
Dim Entity As String
Dim TheVal As Double
Dim X, Y As Long
Dim TheDiff As Double
'Let X = 1
'Let Y = 1
Dim MfgOhProVal As Double

Let X = 25
Let Y = 15
Let MfgOhProVal = 2.31556463


Let Theform = Cells(X, Y).FormulaR1C1
Let TheVal = Cells(X, Y).Value
If LCase(Theform) Like "mfgohpro" Or LCase(Theform) Like "*mfgohpro*" Then
    Let TheAnswer = Replace(Cells(X, Y).FormulaR1C1, "MfgOHPro", MfgOhProVal, 1, -1, vbTextCompare)
    Let Cells(X, Y).Value = TheAnswer
    Let TheDiff = Cells(X, Y).Value - TheVal
    If Round(TheDiff, 2) <> 0 Then
        If TheDiff > 0 Then
            Let Theform = TheAnswer & "-" & TheDiff
            Let Cells(X, Y).formulr1c1 = Theform  'THIS IS THE ROW WHERE I AM CURRENTLY GETTING THE ERROR.
        Else
            MsgBox ("haven't figured this one out yet")
        End If
    End If
          
Else
End If


End Sub

 

[Jonmo1]

Let Cells(X, Y).formulr1c1 = Theform
should be
Let Cells(X, Y).formular1c1 = Theform

 

[MikeDBMan]

Are you kidding me! I spent the last hour trying to figure out what was wrong. And I am just missing an "a"! I owe you big time for finding that! Thanks so much!

 

[Jonmo1]

Glad to help, see my signature..


FYI, the VBA intellisence should help you out with these types of issues.
If a property is spelled correctly, it get's capitalized automatically.

See the difference between the two
Cells(X, Y).formulr1c1
and
Cells(X, Y).FormulaR1C1

even if you type it in all lower case, it get's capitalized automatically when it's spelled correctly.

That should help spot these types of errors in the future.

 

[MikeDBMan]

I am aware of that. I just didn't notice it in my haste to make this quick fix! The operative word is "quick" which is not what I accomplished!