Convert Row Range formula result to Value

ONP Nino

New Member
Joined
Apr 2, 2018
Messages
5
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:

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:

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,328
Office Version
  1. 2010
Platform
  1. Windows
Try replacing all of the code you posted with this single Change event procedure (install it in the worksheet code module)...
Code:
[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]
 
Last edited:

ONP Nino

New Member
Joined
Apr 2, 2018
Messages
5
Genius:)))
It works,
Thank you so much Mr. Rothstein
It has made my day:))
Cheers
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,521
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,310
Messages
5,527,939
Members
409,793
Latest member
mavrik_stet

This Week's Hot Topics

Top