Formulas -> Values (2000 sp-3/ 98SE)

Topic

New Reply

I have been using the following simple macro code to convert formulas to values (for a selection of just a single, contiguous area):

Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

I want to extend this for a multi-area selection (not hard I think, using the Areas collection), but my question is whether there’s a better (more fundamental?) way to convert cell contents from formulas to their resultant values.

thanks

Reply | Quote

Replies

Hi Alan,

To convert an existing range, you could use:
cell.value = cell.value

For example:

Sub Form2Val()
On Error Resume Next
Dim Cell As Range
Dim CellCount As Long
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlCellTypeFormulas)
Cell.Value = Cell.Value
Next Cell
MsgBox “Finished changing ” & CellCount & ” cells.”, 64
End Sub

My own paste values macro is somewhat shorter than yours:

Sub PasteSpecialValues()
On Error Resume Next
Selection.PasteSpecial Paste:=xlValues
End Sub

Whether the paste special approach is quicker than looping through ranges and using cell.value = cell.value is something that could be timed.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

If you don’t have a contiguous selection, your macro will put values in more than just the selection, since you may select the used range.

For example if only 2 noncontiguous cells are chosen, the rows.count*columns.count = 1 and will force the entire used range of the cell to be selected, which may not be what is desired.

You might also want to check for multiple areas in an ElseIf to workd through the cells in each of the areas

A variant of the code from post 394839 could do it (just getting rid of the test for a link.

Steve

Reply | Quote

Steve

Just trying to get this right. Is what you say a result of the .Columns or .Rows property looking at just the first contiguous area of the selection range?

Alan

Reply | Quote

Alan,

Here’s the macro I’ve been using. Seems to work OK on discontiguous selections

Sub PasteValues()
Dim rngFormulas As Range
Dim rngCell As Range
Set rngFormulas = Selection.SpecialCells(xlCellTypeFormulas)
For Each rngCell In rngFormulas
rngCell.Formula = rngCell.Value
Next
Set rngFormulas = Nothing
End Sub

Ken

Reply | Quote

That looks good – it avoids the copy/ paste and specifically targets cells containing formulas. I think this is the one I’m after. Thanks Ken.

Alan

Reply | Quote

That looks good – it avoids the copy/ paste and specifically targets cells containing formulas. I think this is the one I’m after. Thanks Ken.

Alan

Reply | Quote

Alan,

Here’s the macro I’ve been using. Seems to work OK on discontiguous selections

Sub PasteValues()
Dim rngFormulas As Range
Dim rngCell As Range
Set rngFormulas = Selection.SpecialCells(xlCellTypeFormulas)
For Each rngCell In rngFormulas
rngCell.Formula = rngCell.Value
Next
Set rngFormulas = Nothing
End Sub

Ken

Reply | Quote

Steve

Just trying to get this right. Is what you say a result of the .Columns or .Rows property looking at just the first contiguous area of the selection range?

Alan

Reply | Quote

Hi Steve,

Apologies for any confusion – my macro was only intended as a demo of looping through a single range using cell.value = cell.value instead of selecting the range and using pastespecial.values. I wasn’t intending it to meet all of Alan’s requirements.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Steve,

Apologies for any confusion – my macro was only intended as a demo of looping through a single range using cell.value = cell.value instead of selecting the range and using pastespecial.values. I wasn’t intending it to meet all of Alan’s requirements.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

If you don’t have a contiguous selection, your macro will put values in more than just the selection, since you may select the used range.

For example if only 2 noncontiguous cells are chosen, the rows.count*columns.count = 1 and will force the entire used range of the cell to be selected, which may not be what is desired.

You might also want to check for multiple areas in an ElseIf to workd through the cells in each of the areas

A variant of the code from post 394839 could do it (just getting rid of the test for a link.

Steve

Reply | Quote

Thanks Macropod. I guess it’s “suck it and see” wrt efficiency. I generally try to avoid using Selection in macros, but that may not be the most appropriate philosophy here. I presume you meant to include a PasteSpecialValues macro here, with PasteSpecial Paste:=xlValues.

Alan

Reply | Quote

Hi Alan,

Yes, I posted the wrong pastespecial macro. Updated now.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks Macropod (and to Steve & Ken too). Clearly, as with many things VBA, there’s more than one method available. I had several thoughts initially, plus some new ones now, to add to the choices. I think they’ll all work if implemented appropriately, but I’d like to discover which is the best wrt efficiency and good coding practice.

I’m pondering: “Would it me more efficient to use XL’s inbuilt worksheet functions or to loop through cells in a range?” The Copy/ Paste Special operation (your amended method, or my initial one) would require XL to (internally) evaluate all cells in the selected range. But this might end up being more efficient than looping through the same range via VBA, but evaluating only those cells containing formulas. I’d also tried to avoid copy/paste operations in VBA macros, but maybe this “wisdom” is misplaced in this context? I suppose the answer’s only going to come through testing. And it mightn’t matter a flea’s whisker anyway.

Alan

Reply | Quote

How about something simple like :

Range = Range.Value,

where Range is the range to convert.

Andrew C

Reply | Quote

Some interesting results trying to implement this one Andrew. In the attached sheet I have 3 separate ranges, replicated from Row 20 for testing purposes. The macro below seems to do its job on a single, contiguous range, but goes haywire if multiple areas are selected. I can’t figure out why off hand, but will do some debugging.

Public Sub convRange()

    Dim rngFormulas As Range
    Set rngFormulas = Selection.SpecialCells(xlCellTypeFormulas)
    rngFormulas = rngFormulas.Value
    Set rngFormulas = Nothing

End Sub

Alan

Reply | Quote

See my contemporaneous reply to Andrew, which was already addressed by Steve eariler in the thread.

Reply | Quote

Yes, I see the patern. I’m getting replications from Areas(1) in a rather complex fashion – not just from Cells(1,1), but from subsequent cells in that area too. I will take a harder look at what’s actually happening here and try to figure it out. Thanks.

Alan

Reply | Quote

Alan, sorry for giving due consideration to the non-contiguous selection aspect of the problem.

The following should get round that :

    For i = 1 To Selection.Areas.Count
        Selection.Areas(i) = Selection.Areas(i).Value
    Next

Unless the areas the selction conatins a very large number of cells. I’m not sure if there any gain from using SpecialCells(xlCellTypeFormulas), especialy as if there are no formulas in a given area an error will result.

Andrew

Reply | Quote

Thanks Andrew. Looping through the Areas collection was one of the options I considered initially too, As I said, I was really looking for the “best” way of carrying out such an operation, from all of the possibilities available. I’m still not sure of what this would be. As Macropod suggested, timing it might be the appropriate gauge, although results might depend upon factors specific to the selection – as with the case you cite.

As I see it now, it’s a matter of choosing to operate on the whole selection or its areas. In each case, it’s possible to look at contiguous ranges, individual cells or special cells. Too many choices!

Alan

Reply | Quote

Thanks Andrew. Looping through the Areas collection was one of the options I considered initially too, As I said, I was really looking for the “best” way of carrying out such an operation, from all of the possibilities available. I’m still not sure of what this would be. As Macropod suggested, timing it might be the appropriate gauge, although results might depend upon factors specific to the selection – as with the case you cite.

As I see it now, it’s a matter of choosing to operate on the whole selection or its areas. In each case, it’s possible to look at contiguous ranges, individual cells or special cells. Too many choices!

Alan

Reply | Quote

Alan, sorry for giving due consideration to the non-contiguous selection aspect of the problem.

The following should get round that :

    For i = 1 To Selection.Areas.Count
        Selection.Areas(i) = Selection.Areas(i).Value
    Next

Unless the areas the selction conatins a very large number of cells. I’m not sure if there any gain from using SpecialCells(xlCellTypeFormulas), especialy as if there are no formulas in a given area an error will result.

Andrew

Reply | Quote

Yes, I see the patern. I’m getting replications from Areas(1) in a rather complex fashion – not just from Cells(1,1), but from subsequent cells in that area too. I will take a harder look at what’s actually happening here and try to figure it out. Thanks.

Alan

Reply | Quote

See my contemporaneous reply to Andrew, which was already addressed by Steve eariler in the thread.

Reply | Quote

Some interesting results trying to implement this one Andrew. In the attached sheet I have 3 separate ranges, replicated from Row 20 for testing purposes. The macro below seems to do its job on a single, contiguous range, but goes haywire if multiple areas are selected. I can’t figure out why off hand, but will do some debugging.

Public Sub convRange()

    Dim rngFormulas As Range
    Set rngFormulas = Selection.SpecialCells(xlCellTypeFormulas)
    rngFormulas = rngFormulas.Value
    Set rngFormulas = Nothing

End Sub

Alan

Reply | Quote

If range is not contiguous, such as a multi area range name or multiple area selection:

Dim rng As Range
For Each rng In Selection.Areas
rng.Value = rng.Value
Next rng

otherwise Areas after the first are set to the value in Areas(1).Cells(1,1). (Another thing I learned the hard way.)

Reply | Quote

If range is not contiguous, such as a multi area range name or multiple area selection:

Dim rng As Range
For Each rng In Selection.Areas
rng.Value = rng.Value
Next rng

otherwise Areas after the first are set to the value in Areas(1).Cells(1,1). (Another thing I learned the hard way.)

Reply | Quote

How about something simple like :

Range = Range.Value,

where Range is the range to convert.

Andrew C

Reply | Quote

Thanks Macropod (and to Steve & Ken too). Clearly, as with many things VBA, there’s more than one method available. I had several thoughts initially, plus some new ones now, to add to the choices. I think they’ll all work if implemented appropriately, but I’d like to discover which is the best wrt efficiency and good coding practice.

I’m pondering: “Would it me more efficient to use XL’s inbuilt worksheet functions or to loop through cells in a range?” The Copy/ Paste Special operation (your amended method, or my initial one) would require XL to (internally) evaluate all cells in the selected range. But this might end up being more efficient than looping through the same range via VBA, but evaluating only those cells containing formulas. I’d also tried to avoid copy/paste operations in VBA macros, but maybe this “wisdom” is misplaced in this context? I suppose the answer’s only going to come through testing. And it mightn’t matter a flea’s whisker anyway.

Alan

Reply | Quote

Hi Alan,

Yes, I posted the wrong pastespecial macro. Updated now.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks Macropod. I guess it’s “suck it and see” wrt efficiency. I generally try to avoid using Selection in macros, but that may not be the most appropriate philosophy here. I presume you meant to include a PasteSpecialValues macro here, with PasteSpecial Paste:=xlValues.

Alan

Reply | Quote

Hi Alan,

To convert an existing range, you could use:
cell.value = cell.value

For example:

Sub Form2Val()
On Error Resume Next
Dim Cell As Range
Dim CellCount As Long
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlCellTypeFormulas)
Cell.Value = Cell.Value
Next Cell
MsgBox “Finished changing ” & CellCount & ” cells.”, 64
End Sub

My own paste values macro is somewhat shorter than yours:

Sub PasteSpecialValues()
On Error Resume Next
Selection.PasteSpecial Paste:=xlValues
End Sub

Whether the paste special approach is quicker than looping through ranges and using cell.value = cell.value is something that could be timed.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote