CODEWITHSUNDEEP
vbaexcel
k3eper
k3eper

Reputation: 41

DateDiff in VBA for ranges

Ok ill explain what im trying to achieve and what this questions is about specifically.

What am I trying to achieve
The end game is to have an excel spreadsheet that holds a registration date and a date showing when that registry date needs to be updated. Once the updated date is 18months from the registry date and email is sent (via CDO) to an email address captured in the excel spreadsheet.

This Question

Breaking down my end goal into pieces, im currently trying to work out how I get the date calculations done to find out if the 18 months is up yet.

I was heading down this line

Sub TestDatediff()

Dim RegDate As Range
Dim UpDate As Range

Set RegDate = Sheets("Sheet1").Range("G4:G5")
Set UpDate = Sheets("Sheet1").Range("H4:H5")

Answer = DateDiff("m", RegDate, UpDate)
End Sub

RegDate being the initial date, UpDate being the RegDate plus 18months. The Answer would be the result difference between the 2. That would give me a value to do a check on for the next step... the email.

But as you can guess Im not getting anywhere. Ive got a feeling you cant DateDiff a range?

Any help on how to approach the end game, or on how to solve the current issue would be great.

Thanks in Advance for any help

Upvotes: 0

Views: 2168

Answers (2)

pnuts
pnuts

Reputation: 59495

This may help:

Sub LoopRange()
Dim rCell As Range, rRng As Range

Set rRng = Worksheets("DDRegister").Range("g4:g6")
    For Each rCell In rRng.Cells
        rCell.Activate
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=RC[-1]+500"
        ActiveCell.Offset(0, 2).FormulaR1C1 = "=RC[-1]-Today()"
    Next rCell
End Sub

It should take registration dates in G4:G6, add 500 days in H4:H6 respectively and compare H4:H6 with today.

Upvotes: 0

Johnny Bones
Johnny Bones

Reputation: 8414

DateDiff can not be used on a range, only a single value. Perhaps you can point a separate cell at the range and create a single value from it, and then DateDiff that? Or use a variable that does it, like:

UpDate = Sheets("Sheet1").Cells("H4") & Sheets("Sheet1").Cells("H5")

That might need some tweaking since I'm not sure what's in each cell, but you get the idea.

Upvotes: 1

Related Questions