VBA deletes formulas in range

[Trixafett]

How do i retain it from changing my formulas into Values.
The only value change should be (i, 14) or Column N

Option Explicit

Sub btnStk_Click()
    Dim rData As Range, arrData
    Dim i As Long
    Dim sName As Long
    Dim sNote As String
    Set rData = Sheets("Inventory").Range("A3").CurrentRegion
    arrData = rData.Value
    For i = 2 To UBound(arrData)
        If Len(arrData(i, 14)) > 0 And VBA.IsNumeric(arrData(i, 14)) Then
            arrData(i, 7) = arrData(i, 7) + arrData(i, 14)
            If Val(arrData(i, 14)) > 0 Then
                sNote = "Stock Added"
            Else
                sNote = "Stock Removed"
            End If
            ' Clear column C
            Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1, 5).Value _
                = Array(Now(), Environ("UserName"), sNote, arrData(i, 3), arrData(i, 14))
            arrData(i, 14) = ""
        End If
    Next
    rData.Value = arrData
End Sub

 

[Legacy 143009]

Hi,

Try like this:

Option Explicit
Sub btnStk_Click()
    Dim rData As Range, arrData
    Dim i As Long
    Dim sName As Long
    Dim sNote As String
    Set rData = Sheets("Inventory").Range("A3").CurrentRegion
    arrData = rData.Formula
    For i = 2 To UBound(arrData)
        If Len(arrData(i, 14)) > 0 And VBA.IsNumeric(Evaluate(arrData(i, 14))) Then
            arrData(i, 7) = Evaluate(arrData(i, 7)) + Evaluate(arrData(i, 14)) 'This must stay as Value since you are casting to a value directly.
            If Val(Evaluate(aarrData(i, 14))) > 0 Then
                sNote = "Stock Added"
            Else
                sNote = "Stock Removed"
            End If
            ' Clear column C
            Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1, 5).Value _
                = Array(Now(), Environ("UserName"), sNote, Evaluate(arrData(i, 3)), Evaluate(arrData(i, 14)))
            arrData(i, 14) = ""
        End If
    Next
    rData.Formula = arrData
End Sub

 

[Trixafett]

Hi,

Try like this:

Option Explicit
Sub btnStk_Click()
    Dim rData As Range, arrData
    Dim i As Long
    Dim sName As Long
    Dim sNote As String
    Set rData = Sheets("Inventory").Range("A3").CurrentRegion
    arrData = rData.Formula
    For i = 2 To UBound(arrData)
        If Len(arrData(i, 14)) > 0 And VBA.IsNumeric(Evaluate(arrData(i, 14))) Then
            arrData(i, 7) = Evaluate(arrData(i, 7)) + Evaluate(arrData(i, 14)) 'This must stay as Value since you are casting to a value directly.
            If Val(Evaluate(aarrData(i, 14))) > 0 Then
                sNote = "Stock Added"
            Else
                sNote = "Stock Removed"
            End If
            ' Clear column C
            Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1, 5).Value _
                = Array(Now(), Environ("UserName"), sNote, Evaluate(arrData(i, 3)), Evaluate(arrData(i, 14)))
            arrData(i, 14) = ""
        End If
    Next
    rData.Formula = arrData
End Sub

Hi thanks for the quick response, the ones i need to add together are values, the rest in the array are formulas that need not be touched.
so it does not work with .Formula

 

[Alex Blakenburg]

Is there any chance you just mean you only want to write back column 14 ?

rData.Columns(14).Value = Application.Index(arrData, 0, 14)

Note: You also seem to be updating column 7, so you would want to do the same thing for that column.