PDA

View Full Version : Convert Row Range formula result to Value



Onp
04-02-2018, 01:38 PM
Convert Formula Result into Value

Hello,
I am not too familiar with VBA, I am using this sample code. However its going to be a very long VBA code if I enter all the columns I need converted to value. (See Sample) And the computer although a fast one is taking forever to calculate each row. like 10 Hours. https://www.mrexcel.com/forum/images/smilies/frown.png

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 paste or type a "1" in column C one row at the time.
So,
If I enter "1" in C12 then Row D12 to BA12 should convert formula result to Value
if I enter "1" in C13 then Row D13 to BA13 should convert formula result to Value
and same goes for all the 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 helphttps://www.mrexcel.com/forum/images/smilies/icon_smile.gif
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

Onp
04-02-2018, 02:05 PM
Thank you all
This matter has been resolved with the following code
Cheers

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