fundamental differences in codes

[RSXchin]

is there any real difference (if I'm only worrying about values) between

    With Range("A1:A10")
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With

and

    With Range("A1:A10")
        .Formula = .Value
    End With

?

 

[VoG]

The values should be the same. The second should be faster.

 

[Jonmo1]

I think you would want to use

.Value = .Value (not .Formula = .Value)


1 advantage to doing Value = Value instead of copy / pastespecial

When you have formulas returning ""
And you copy / PasteSpecial / Values
Those "" are NOT Truely Blank, they are NULL Text Strings.
So things like IsBlank will NOT see them as blank.

.Value = .Value resolves that issue.

 

[RSXchin]

awesome!!! that'll make my codes easier to understand for my replacements anyway. Are there any limitations with that?

i.e. if I wanted to do it with the entire sheet, would it give me a memory overflow or something like it?

 

[RSXchin]

I think you would want to use

.Value = .Value (not .Formula = .Value)


1 advantage to doing Value = Value instead of copy / pastespecial

When you have formulas returning ""
And you copy / PasteSpecial / Values
Those "" are NOT Truely Blank, they are NULL Text Strings.
So things like CountBlank will NOT see them as blank.

.Value = .Value resolves that issue.

.Value = .Value

== awesomer!

Thank you

 

[Jonmo1]

I don't know, I've never tested it's limits.
You tell us..

It may have problems with formula errors...maybe..

 

[RSXchin]

I misspoke when I said ".value = .value"

Because that leaves the formulas. That's not what I wanted at all... I'll stick with ".Formula = .Value"

 

[Jonmo1]

I misspoke when I said ".value = .value"

Because that leaves the formulas. That's not what I wanted at all... I'll stick with ".Formula = .Value"

No it doesn't.

The only way I can make .value = .value result with a formula in a cell is if I enter a formula as a text string beginning with an apostrophe like

'=SUM(B1:B10)

With that in the cell, it is NOT a formula, it's viewed as a text string
Then the .value = .value makes it a formula
But .Formula = .Value does as well.

If I do the .value = .value again, then it removes the formula, and leaves the result of the formula.