Converting a range of formulae to values

[NewOrderFac33]

Good afternoon,
I have the following procedure which adds a formula to a range, then attempts to convert the resulkts of those formulae into values.

Sub AlignAndFormatTotalLeadTimes()
    Set TotalLeadTime = Sheets("Database").Range("Col_DB_TotalLeadTime")
    'Right align apply number format and remove "NULL" or zero values from Total LeadTime column
    With Range("Col_DB_TotalLeadTime")
        .NumberFormat = "[h]""h"" mm""m"" ss""s"""
        .HorizontalAlignment = xlRight
        .FormulaR1C1 = "=IF(OR(RC[-3]=0,RC[-1]=0),""-"",RC[-1]-RC[-3])"
        .Formula = .value
    End With
End Sub

However, I would expect the" .value" to be capitalized i.e. ".Value", but it just seems to be ignored and doesn't snap into place like I'd expect it to.

Can anyone suggest what I'm doing wrong, please?

Thanks in advance

Pete

 

[Joe4]

Try replacing:

.Formula = .value

with

.Value = .Value

 

[Fluff]

However, I would expect the" .value" to be capitalized i.e. ".Value"

That would suggest you have (at some time) created a variable or procedure called value.
If you type Dim Value at the top of the code & hit enter, the capitalisation should return. You can then delete the Dim statement.

 

[NewOrderFac33]

That would suggest you have (at some time) created a variable or procedure called value.
If you type Dim Value at the top of the code & hit enter, the capitalisation should return. You can then delete the Dim statement.

That's it! For some bizarre reason, I have a Public function that declares "value" as a variant type (probably copied from here or StackOverflow and I never renamed the variables.
I've renamed it, and all the .value" s have disappeared.
I'd NEVER have figured that out - thanks very much!
Pete

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[MARK858]

probably copied from here

I hope not