Search code examples
arraysdateexceldateaddvba

Excel, VB - Subtract Days from Date Using DateAdd()


ISSUE

I wish to subtract a 1 to 3 digit number stored in an array from a date field in the worksheet. I take the absolute value of the number, multiply it by negative 1, then use the DateAdd function to perform the operation. I receive no error messages, but the array value remains the exact 1 to 3 digit number originally sent through the process.

EXAMPLE

Column C is what my current results are giving me.

        A        B        C
1  1/8/09       54       54
2  3/3/11        1        1
3  8/1/10      132      132 

CODE

If delType = "Numeric" Then
    ElseIf delChars = 3 Or delChars = 2 Or delChars = 1 Then
         del(i, 1) = Abs(del(i, 1))
         del(i, 1) = del(i, 1) * -1
         del(i, 1) = DateAdd("d", del(i, 1), Range("E" & i + 1))
         'I did confirm that this case is actually working 
         'by setting the above line to del(i,1) = "Digits" and
         'received "Digits" for all entries with 1 to 3 numeric digits.
    End If
End If

Solution

  • Sub Tester()
    
        Dim arr, x
    
        arr = Range("A1:A3").Value 'values to be subtracted
    
    
        For x = 1 To UBound(arr, 1)
            ' "base" date is in A5
            arr(x, 1) = Range("A5").Value - Abs(arr(x, 1))
        Next x
    
        With Range("D1:D3")
            .NumberFormat = "m/d/yyyy"
            .Value = arr 'dump values back to sheet
        End With
    
    End Sub