Changing cell from formula to text

[PeterBrazel]

I am developing a spreadsheet that records transactions and assists with reconciliation.

When the statement is received the last transaction is marked in the database. The statement multi currency balances are then entered into a reconciliation sheet. The balance sheet indicates a "Balanced" or "Out" below the statement balance.

Back on the transaction listing I could have a formula that looks to see that the account is balanced and then ticks eached balanced transaction.

Once this has happened and the operator is content I want some sort of macro to be spreadsheet selected that converts the formula into a permanent tick. In this way once a transaction has been reconciled it stays that way.

Any suggestions.

Thanks.

Peter Brazel

_________________


[ This Message was edited by: PeterBrazel on 2002-05-14 20:56 ]

[Derek]

Hi there

You could use macro code like this where the range/s are selected first:

For Each cell In Selection
cell.Formula = cell.Value
Next
End Sub

Or if it is a set range you could use:

For Each cell In Range("B3:B16,B21,B30")
cell.Formula = cell.Value
Next
End Sub

Hope this helps

regards
Derek

[Tom Schreiner]

Without some more details it will be somewhat difficult to give you a decent code example.
If you select the range with the info you want converted from formula to text, copy it, and then paste special(values). Only the values returned from the formulas will remain in these cells...

In a macro, Range("A1:A10") for instance, to convert this range from formulas to values, use something like this...

Range("A1:A10") = Range("A1:A10").Value

Hope this helps.
If not, please post some more details.
Thanks,
Tom

[Nimrod]

Here are 3 different examples...

Sub ConvertCell()
        With Range("A1")
        .Copy
        .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        End With
End Sub
Sub ConvertSheet()
        With Cells
        .Copy
        .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        End With
End Sub

Sub ConvertNamedRange()
        With Range(NamedRange)
        .Copy
        .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        End With
End Sub

[Nimrod]

Cool Derik ... Great way ... I remember that one.

[NateO]

To minimize the loop, let's only loop through formulae:

sub st()

dim frmcl as range, cl as range

on error resume next

Set FrmCl = activesheet.[a1].SpecialCells(xlFormulas)

    If FrmCl Is Nothing Then GoTo 1

    For Each cl In FrmCl

      cl.value = cl.value

    next cl

end

1: msgbox "No formulae found!"

end sub

Skips all non-formula cells.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-14 21:30 ]

[Colo]

Hi all,

If the range is large(like 100 cols * 5000 rows), to use array make execute fast.

Sub SlectionFtoV()

    Dim varSelected

    Application.ScreenUpdating = False

    With Selection

        If .Count = 1 Then .Value = .Value: Exit Sub

        varSelected = .Value

        .Value = varSelected

    End With

    Application.ScreenUpdating = True

End Sub