Subtract dates VBA

[Av8tordude]

I'm using a formula to subtract dates from today. I would like to use a vba to go through each row (I3:I12) to calculate the days remaining from the dates in column (H3:H12). Greeatly appreciate any assistance. Thank you kindly

 

[ranman256]

vba:
=DateDiff("d", date1, date2)

 

[MARK858]

Why use VBA when you can use formula? if it is days then just Date2-Date1 or Date2-(Date1-1) depending whether you include the start day or not.
Btw the syntax if you are using DATEDIF (DATEDIF not DATEDIFF) in formula is DATEDIF(date1,date2,"d")

Excel 2010
	A	B	C
1	01/10/2018		19
2	20/10/2018		19
3			20
Sheet1

Cell Formulas
Range	Formula
C1	=DATEDIF(A1,A2,"d")
C2	=A2-A1
C3	=A2-(A1-1)

Btw, you can just subtract one date from the other in VBA as well if it is days rather than use DATEDIFF. No need for the function call.

 

[Scott Huish]

DATEDIF is the spreadsheet function. DATEDIFF is the VBA function.

 

[MARK858]

Thats why I stated

in formula

 

[Av8tordude]

I search and found a code similar to what I want to achieve (see below). I tried tiredlessly to alter it to my needs, but i'm at a loss. I would like the code to only be used from Row 3-12. Aside from that, the code achieves the task that I'm looking for. In case any inquiries as to what I will be using the code for is to run it when the sheet is activated. Can someone assist with my request. Thanks.

Sub Test1()Dim LastRow  As Long, i As Long
With Worksheets("Options")
    LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
    
    For i = 3 To LastRow
        .Range("I" & i).Value = DateDiff("d", .Range("H" & i).Value, Date)
    Next i
End With
End Sub

 

[Av8tordude]

One other thing, sometimes a row will be empty, so I don't want the calculation to occurs if a cell in the Range H3:H12 is empty.

 

[MARK858]

Sub Test1a()
    Dim i As Long
    With Worksheets("Options")
        For i = 3 To 12
            If .Range("H" & i).Value <> "" Then
                .Range("I" & i).Value = Date - .Range("H" & i).Value
            End If
        Next i
    End With
End Sub

or

Sub Test1b()
    Dim i As Long
    With Worksheets("Options")
        For i = 3 To 12
            If .Range("H" & i).Value <> "" Then
                .Range("I" & i).Value = DateDiff("d", .Range("H" & i).Value, Date)
            End If
        Next i
    End With
End Sub

The code above runs from a regular module but if by

run it when the sheet is activated

you mean you want the code to run automatically then it needs a total rewrite.

 

[Av8tordude]

Works perfect Mark! Yes, I will be placing the code in the Worksheet Activate to run when the sheet is activated. Thank you very much everyone. Sincerely appreciated it. Out of curiosity, what is the difference between the two codes?

Sub Test1a()
    Dim i As Long
    With Worksheets("Options")
        For i = 3 To 12
            If .Range("H" & i).Value <> "" Then
                .Range("I" & i).Value = Date - .Range("H" & i).Value
            End If
        Next i
    End With
End Sub

or

Sub Test1b()
    Dim i As Long
    With Worksheets("Options")
        For i = 3 To 12
            If .Range("H" & i).Value <> "" Then
                .Range("I" & i).Value = DateDiff("d", .Range("H" & i).Value, Date)
            End If
        Next i
    End With
End Sub

The code above runs from a regular module but if by you mean you want the code to run automatically then it needs a total rewrite.

 

[Av8tordude]

Disregard..I see the difference. Thank you very much Mark