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

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

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

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

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

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

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