Worksheet change that recognizes a formula in the target range

Question

I'm using the following worksheet change to copy information from Sheet 1 to Sheet 2 if Range C:C has a date in it. But I'd like to insert a Time Stamp formula in Range C:C to automatically generate the date. I was wondering how I can modify my VBA code so that it will recognize the formula that would now be in that range? Thanks!!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nextRow As Long
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
        If VBA.IsDate(Target) Then
            With Worksheets("Summary")
                nextRow = IIf(VBA.IsEmpty(.Range("A1048576").End(xlUp)), 1, .Range("A1048576").End(xlUp).Row + 1)
                .Range("A" & nextRow) = Target.Offset(0, -2)
                .Range("B" & nextRow) = Target.Offset(0, -1)
                .Range("E" & nextRow) = Target
                .Range("H" & nextRow) = Target.Offset(0, 5)
            End With
        End If
    End If
End Sub

Isaac Moses Isaac Moses · Accepted Answer · 2015-02-03T17:31:26

The code you have ought to work as-is. The IsDate function "returns True if [the expression you pass in] is of the Date Data Type or can be converted to Date." A Range whose value is a date can be converted to Date, so cells with your date-returning formula in them should be recognized. (I verified this experimentally.)

The only caveat is that if you want to copy over the value rather than the formula (possibly essential if the formula is context-dependent), you should replace

.Range("E" & nextRow) = Target

with

.Range("E" & nextRow) = Target.Value

and similarly append .Value after any of the Offsets that may contain formulas you don't want to copy.

Worksheet change that recognizes a formula in the target range

1 Answers