.value = .value

[awsumchillicrab]

Hi,

I've used

.Value = .Value

a few times to replace a range from being a formula to just the values.

However, sometimes it seems to delete my entire formula, even though I use the same syntax! Can anyone explain to me what this .value = .value really does?


Here's one where it effectively pasted values only:

in autofiltered mode

With Range("Q2:Q" & LastRow).SpecialCells(xlCellTypeVisible)
.FormulaR1C1 = "=IF(RC[-5]>RC[-9]+21,""Improve date"","""")"
.Value = .Value
End With

And here's one where it deleted my formulas:

in a different autofiltered mode

With Range("Q2:Q" & LastRow).SpecialCells(xlCellTypeVisible)
.FormulaR1C1 = "=IF(networkdays(RC[-9],today())>4,""No promise date"","""")"
.Value = .Value
End With

 

[JackDanIce]

I don't understand what you mean. If you have a cell and you enter a formula into it and then copy and paste special the values into the same cell, isn't that action going to replace the formula with the value i.e. delete the formula?

Your two code examples are virtually identical by nature of what they are doing so I don't understand the two differences you state of

"Here's one where it effectively pasted values only:"

vs

"And here's one where it deleted my formulas:"

As suggestion, you could replace the WITH END WITH statement and turn it into a FOR NEXT loop and then use F8 to step through the loop to understand what the code is doing in your two examples below

 

[awsumchillicrab]

"And here's one where it deleted my formulas:"

What i meant was that it just erased the cell. There would be no value and formula in the cell anymore.

I could go ahead and enter a code for copying the range, then pasting special, but up 'til this issue, the statement .Value = .Value seemed to work.

 

[njimack]

If your formula is returning nothing ("") then replacing that formula with it's value would result in a completely blank cell.

 

[Weaver]

Just wondering if you're calculating the sheet. I.e, is calculation set to auto or manual. Could the settings be different from one use to the other?

 

[awsumchillicrab]

No I scripted my test-sheet to have one cell "" and one cell with "No promise date".

When I add .Value = .Value, both cells become empty with no formula.
Without it, both cells have the formula, yielding the 2 different results.

I don't really know what manual/auto calculation is.

Anyhow, would people generally use a copy-paste code sequence to do a paste special values?

 

[wigi]

Anyhow, would people generally use a copy-paste code sequence to do a paste special values?

No, but can you please provide the VBA-code to reproduce the behaviour you see?

 

[Weaver]

Isn't =networkdays() from the analysis toolpak or another plugin?

I wonder if this has any bearing on the results you're getting?

Can you try it with a basic Excel function instead and see what happens?

Of course, I could be clutching at straws...

 

[awsumchillicrab]

Wigi,

I'm not sure if you meant the entire code, but the relevant part is below. I could give the whole code if necessary.
The sheet has columns up to Q.
Column M has some blanks, some non-blanks.
Column L has some cells with 00/00/0000 (in General format)
Column H is a bunch of dates, representing the RC[-9] portion.

ActiveSheet.Range("A1").AutoFilter field:=13, Criteria1:="" ' ZOT1
ActiveSheet.Range("A1").AutoFilter field:=12, Criteria1:="00/00/0000" 'cprom is 00/00/0000"

On Error Resume Next
With Range("Q2:Q" & LastRow).SpecialCells(xlCellTypeVisible)
.FormulaR1C1 = "=IF(networkdays(RC[-9],today())>4,""No promise date"","""")"
.Value = .Value
End With

ActiveSheet.AutoFilterMode = False


Weaver, I changed to networkdays line to this:

.FormulaR1C1 = "=IF(RC[-9]<today(),""No promise date"","""")"

and this:

.FormulaR1C1 = "=IF(RC[-9]<40707,""No promise date"","""")"

But again, the .Value = .Value messes it up.
By the way, I'm on Excel 2007 and didn't have to install the extra analysis pack. I know 2003 needed to.

 

[JackDanIce]

value = .value

On a brand new sheet, set the following up:

Cell A1: 1
Cell B1: =IF(A1=1,"TRUE","")
Cell C1: =LEN(B1)

Cell A2: 0
Cell B2: =IF(A2=1,"TRUE","")
Cell C2: =LEN(C1)

Now use F8 to step through the following code 1 line at a time, DO NOT just run the macro:

Sub Spud()

Dim i As Long

For i = 1 To 2
    With Range("D" & i)
        .Select
        .Formula = "=IF(A" & i & "=1,""TRUE"","""")"
    End With
    With Range("E" & i)
        .Select
        .Formula = "=LEN(D" & i & ")"
    End With
Next i
MsgBox "The macro will now remove the formula it just placed into column D"
With Range("D1:D2")
    .Select
    .Value = .Value
End With
    
MsgBox "The values in column E have not changed, even though we have used .value = .value in column D"
MsgBox "The macro will now use copy + paste values, watch Column E again"
For i = 1 To 2
    With Range("D" & i)
        .Select
        .Formula = "=IF(A" & i & "=1,""TRUE"","""")"
    End With
    With Range("E" & i)
        .Select
        .Formula = "=LEN(D" & i & ")"
    End With
Next i
With Range("D1:D2")
    .Select
   .Copy
   .PasteSpecial Paste:=xlPasteValues
End With
MsgBox "The values in column E have not changed, but we have now used pastespecial values in column D"

End Sub

Hopefully this will show you that there is fundamentally no difference between .value = .value and copy + pastespecial values.

I think whatever issue you have is something to do with your sheet or how you've set it up.

Manual calculation vs automatic is as it reads. Your worksheet can be set to automatically calculate every formula it contains or if set to manual, will only make calculations when you press F9. So if it's manual and you have a formula, you can change an input value for that formula, but the output WILL NOT update, unless you press F9 (if in manual mode)