[VBA] Capture a value before a cell converts it into date format

[Snypa]

Hello,

I want to write some VBA that does the following:

user enters an integer into a cell
store the integer
change the cell value to '=today()-integer'

I have pretty much got everything I need written, the problem is, I am using Target.Value, which is showing the integer converted into date format. I.e: 5 becomes 04/01/1900, 100 becomes 09/04/1900 etc.

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' Cells
    Set KeyCells = Range("B4:F9")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Code here
        If IsNumeric(Target.Value) Then
            Range(Target.Address).Select
            ActiveCell.FormulaR1C1 = "=today()-" & Target.Value
        End If
     
    End If
End Sub

 

[My Aswer Is This]

Lets forget about this script and tell me what your wanting to do.

 

[Snypa]

Lets forget about this script and tell me what your wanting to do.

Thank you,

I have a group of cells that have date values in them. Each morning I must work out the oldest of a set of data and enter it into the cells.

So basically, I am writing =today()-x in a bunch of cells.

I want to only have to type x into the cells and have the cell change it to =today()-x

Does that make sense?

 

[Fennek]

Hi,

maybe is better to use vba only

target.value = cdbl(date) + target.value

regards

 

[FormR]

I have pretty much got everything I need written, the problem is, I am using Target.Value, which is showing the integer converted into date format. I.e: 5 becomes 04/01/1900, 100 becomes 09/04/1900 etc.

Hi, try like this instead.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    ' Cells
    Set KeyCells = Range("B4:F9")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        ' Code here
        If IsNumeric(Target.Value2) Then
            Application.EnableEvents = False
            Target.Formula = "=today()-" & Target.Value2
            Application.EnableEvents = True
        End If
     
    End If
End Sub

 

[FormR]

Hi, try like this instead.

In fact - this is probably better.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, C As Range
Set R = Application.Intersect(Range("B4:F9"), Target)
If Not R Is Nothing Then
    For Each C In R
        If IsNumeric(C.Value2) And C.Value2 <> "" Then
            Application.EnableEvents = False
            C.Formula = "=TODAY()-" & C.Value2
            Application.EnableEvents = True
        End If
    Next C
End If
End Sub

 

[Snypa]

Thank you @FormR, works like a charm.

Also thank you @Fennek