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

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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