Convert Row Range formula result to Value

[ONP Nino]

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:

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

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

 

[Rick Rothstein]

Try replacing all of the code you posted with this single Change event procedure (install it in the worksheet code module)...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Target.Column = 3 Then
    If Target.Row > 11 And Target.Row < 5001 Then
      For Each Cell In Intersect(Target, Columns("C"))
        If Cell.Value = 1 Then Cell.Offset(, 1).Resize(, 50).Value = Cell.Offset(, 1).Resize(, 50).Value
      Next
    End If
  End If
End Sub[/td]
[/tr]
[/table]

 

[ONP Nino]

Genius))
It works,
Thank you so much Mr. Rothstein
It has made my day)
Cheers

 

[Fluff]

FWIW
Cross posted http://www.vbaexpress.com/forum/showthread.php?62409-Convert-Row-Range-formula-result-to-Value

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.