Consulting

Results 1 to 2 of 2

Thread: Convert Row Range formula result to Value

  1. #1
    VBAX Newbie
    Joined
    Apr 2018
    Posts
    2
    Location

    Convert Row Range formula result to Value

    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.

    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 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

  2. #2
    VBAX Newbie
    Joined
    Apr 2018
    Posts
    2
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •