Convert Formula Result into Value

Hello,

I am not to familiar with VBA, but I managed to use the current VBA to accomplish what I desire. However its going to be a very long VBA script if I enter all the rows I need. (See Sample) And the computer although a fast one is taking forever to calculate each row. like 10 Hours.

Example:

In column 3 (C) each time I type a value such as "1" starting from C12 then next C13 then C14 an on till C5000 each Row from D12 to BA12 have different formulas that produce a result. at this point I would like the results to convert into Values Starting from D12 to BA12. But such should only happen when I enter a "1"

So,

If I enter "1" in C12 then Row D12 to BA12 converts to Value after having calculated

if I enter "1" in C13 then Row D13 to BA13 converts to value after having calculated

and same goes for all rows down to C5000.

and if possible I like this tho happen on 3 sheets out of 5 sheets in a workbook.

Thank you in advance for your help

Cheers

Nino

Code i am Using:

Hello,

I am not to familiar with VBA, but I managed to use the current VBA to accomplish what I desire. However its going to be a very long VBA script if I enter all the rows I need. (See Sample) And the computer although a fast one is taking forever to calculate each row. like 10 Hours.

Example:

In column 3 (C) each time I type a value such as "1" starting from C12 then next C13 then C14 an on till C5000 each Row from D12 to BA12 have different formulas that produce a result. at this point I would like the results to convert into Values Starting from D12 to BA12. But such should only happen when I enter a "1"

So,

If I enter "1" in C12 then Row D12 to BA12 converts to Value after having calculated

if I enter "1" in C13 then Row D13 to BA13 converts to value after having calculated

and same goes for all rows down to C5000.

and if possible I like this tho happen on 3 sheets out of 5 sheets in a workbook.

Thank you in advance for your help

Cheers

Nino

Code i am Using:

Code:

```
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
If Target.Column = 3 Then
With ActiveSheet
For Each cell In Target.Offset(-11, 2).Resize(40, 1)
If cell.Value <> 0 Then cell.Formula = cell.Value
Next cell
End With
End If
End Sub
```

Code:

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strCol As String
If Not Intersect(Target(1, 1), Range("c:c")) Is Nothing Then
Select Case Target(1, 1).Column
Case 3: strCol = "f"
End Select
Cells(Target(1, 1).Row, strCol) = Cells(Target(1, 1).Row, strCol).Value
End If
If Not Intersect(Target(1, 1), Range("c:c")) Is Nothing Then
Select Case Target(1, 1).Column
Case 3: strCol = "g"
End Select
Cells(Target(1, 1).Row, strCol) = Cells(Target(1, 1).Row, strCol).Value
End If
If Not Intersect(Target(1, 1), Range("c:c")) Is Nothing Then
Select Case Target(1, 1).Column
Case 3: strCol = "h"
End Select
Cells(Target(1, 1).Row, strCol) = Cells(Target(1, 1).Row, strCol).Value
End If
If Not Intersect(Target(1, 1), Range("c:c")) Is Nothing Then
Select Case Target(1, 1).Column
Case 3: strCol = "i"
End Select
Cells(Target(1, 1).Row, strCol) = Cells(Target(1, 1).Row, strCol).Value
End If
If Not Intersect(Target(1, 1), Range("c:c")) Is Nothing Then
Select Case Target(1, 1).Column
Case 3: strCol = "j"
End Select
Cells(Target(1, 1).Row, strCol) = Cells(Target(1, 1).Row, strCol).Value
End If
If Not Intersect(Target(1, 1), Range("c:c")) Is Nothing Then
Select Case Target(1, 1).Column
Case 3: strCol = "k"
End Select
Cells(Target(1, 1).Row, strCol) = Cells(Target(1, 1).Row, strCol).Value
End If
End Sub
```

Last edited by a moderator: