Leave only a Value after running a Formula

[amerifax-heather]

When running a Formula, is there anyway to just leave the value after the formula has been run. We are processing a very large data file and it is a huge operation just to copy and paste the value into another column and delete the original column with the formula.

This is an example of the formula:
=IF(COUNTIF(D:D,D2)>1,"X","")

When done we would like to be left with the Value-X and not the formula.

Thanks,

Heather

 

[JEB85]

Hi,

Just highlight the range you want to convert to values ==> ctrl c ==> right click ==> paste special ==> values ==> OK

 

[amerifax]

Hi,

Just highlight the range you want to convert to values ==> ctrl c ==> right click ==> paste special ==> values ==> OK

I believe she meant with in the calling formula. We are using the Ctr C etc but it can be cumbersome. We where hoping that the cell with the formula could end up ,after ran, with only the value instead of both formula and value.

Thanks
Bob

 

[JackDanIce]

Bob/Heather,

In that case a macro would probably be better suited.

If you can advise what column you are currently placing the formula in, then replacing with the output values (copy + paste) and then deleting, I or someone else on the board, could write a macro for you so you'd just need to run a single command/press a single button the macro is assigned to.

Jack

 

[amerifax]

I was thinking that a Macro might do it. I will talk to Heather in the AM.
Thanks
Bob

 

[JackDanIce]

Fair enough. Your code will probably be something like:

Sub FormulaToValue ()
 
Dim i As Long
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
i = Range("D" & Rows.Count).End(xlUp).Row
 
With Range("E1")
    .Formula = "=IF(COUNTIF(D1:D" & i & ",D2)>1,""X"","""")"
End With
 
With Range("E1:E" & i)
    .FillDown
    .Value = .Value
End With
 
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
 
End Sub

 

[JackDanIce]

Or this - lots of different ways:

Sub FormulasToValues()
Dim i As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
For i = 1 To Range("D" & Rows.Count).End(xlUp).Row
    If Range("D" & i) > 1 Then
        Range("E" & i) = "X"
    Else
        Range("E" & i) = ""
    End If
Next

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

 

[amerifax]

I will try both on Monday