.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

 

[RoryA]

Re: value = .value

You should never use .Value = .Value (or any other assignment of an array to the Value) with a filtered dataset as you will end with strange results, depending on which rows are visible. (you will end up with the array of the first visible area being put into every area so far as it will fit.)

 

[awsumchillicrab]

so the solution would be more like copy visible cells, and paste onto visible cells?

I don't even think excel can do that right? even in non-VBA mode.

I'm guessing the solution then is to unfilter, then paste values for the entire row including blanks?


Thanks Jack for the code. Yeah it shows me that the .value = .value is essentially pasting values. But 'cause .value = .value is shrouded in mysterious code without saying "copy this paste that", and with it sounding like a circular argument, i wasn't sure if it truly only pastes values regardless the scenario.

 

[RoryA]

You would need to loop through each Area and use the .Value = .Value on that.