Convert IF formulas to their values
 

I have a worksheet with various formulas. I want to convert only the
"IF" formulas to their values. Is it possible and if so how? TIA

Greg

Convert IF formulas to their values
 

Hi Greg,

Try:

'=============
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set SH = ActiveSheet
On Error Resume Next
Set rng = Sh.Cells.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
With rCell
.Select
If Left(.Formula, 3) = "=IF" Then
.Value = .Value
End If
End With
Next rCell
End If
End Sub
'<<=============


---
Regards,
Norman



"GregR" wrote in message
ups.com...
I have a worksheet with various formulas. I want to convert only the
"IF" formulas to their values. Is it possible and if so how? TIA

Greg

Convert IF formulas to their values
 

Norman, worked like a champ. Thank you

Greg
Norman Jones wrote:
Hi Greg,

Try:

'=============
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set SH = ActiveSheet
On Error Resume Next
Set rng = Sh.Cells.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
With rCell
.Select
If Left(.Formula, 3) = "=IF" Then
.Value = .Value
End If
End With
Next rCell
End If
End Sub
'<<=============


---
Regards,
Norman



"GregR" wrote in message
ups.com...
I have a worksheet with various formulas. I want to convert only the
"IF" formulas to their values. Is it possible and if so how? TIA

Greg

Convert IF formulas to their values
 

Hii Greg,

Please delete:

.Select

This line was only included for testing purposes!

However, try the following version which is more robust and which caters for
multiple forms of IF formula:

'=============
Public Sub Tester2()
Dim Sh As Worksheet
Dim rng As Range
Dim rCell As Range

Set Sh = ActiveSheet
On Error Resume Next
Set rng = Sh.Cells.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
With rCell
If .Formula Like "*IF*" Then
.Value = .Value
End If
End With
Next rCell
End If
End Sub
'<<=============


---
Regards,
Norman

Convert IF formulas to their values
 

Norman, just one question, what is the 23 in the celltypeformulas and
are there other numbers that have a meaning. TIA

Greg
Norman Jones wrote:
Hii Greg,

Please delete:

.Select

This line was only included for testing purposes!

However, try the following version which is more robust and which caters for
multiple forms of IF formula:

'=============
Public Sub Tester2()
Dim Sh As Worksheet
Dim rng As Range
Dim rCell As Range

Set Sh = ActiveSheet
On Error Resume Next
Set rng = Sh.Cells.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
With rCell
If .Formula Like "*IF*" Then
.Value = .Value
End If
End With
Next rCell
End If
End Sub
'<<=============


---
Regards,
Norman

Convert IF formulas to their values
 

Hi Greg,

Norman, just one question, what is the 23 in the celltypeformulas and
are there other numbers that have a meaning. TIA

Each of the SpecialCells method constants have numeric values. These
constants can be summed to include multiple options. The value 23 is the
result of adding all of the xlCellTypeFormulas contants and is equvalent, in
Excel, to checking all of the formula options.

---
Regards,
Norman

Convert IF formulas to their values
 

Norman, thanks...................the lessons just keep coming

Greg
Norman Jones wrote:
Hi Greg,

Norman, just one question, what is the 23 in the celltypeformulas and
are there other numbers that have a meaning. TIA

Each of the SpecialCells method constants have numeric values. These
constants can be summed to include multiple options. The value 23 is the
result of adding all of the xlCellTypeFormulas contants and is equvalent, in
Excel, to checking all of the formula options.

---
Regards,
Norman