0

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

k3eper
  • 41
  • 1
  • 8
  • 1
    what are in the two cells? Is month in one and year in the other? If you can give a screenshot of your data layout (or a text explanation of that's in both cells for each range), it'll help for sure. – sous2817 Sep 12 '13 at 15:52

2 Answers2

1

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.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • Thanks all for the responses so far. Data in the cells is 11/09/2013 24/01/2015 12/05/2001 24/09/2002 – k3eper Sep 13 '13 at 15:24
  • Yikes! Yeah, I don't see that happening. Put the RegDate in one column, and then you can calculate the UpDate by using DateAdd. Then you can look at the UpDate column and compare it to today's date. – Johnny Bones Sep 13 '13 at 15:29
  • Thanks all for the responses so far. Data in the cells is 11/09/2013 24/01/2015 I get the second value by doing adding 500 to the original date. So thinking about it, I really need to automate the checking of that secondvalue with "NOW" if its a month before that time send an automated email. – k3eper Sep 13 '13 at 15:38
  • 1
    ok that seems a better approach. Ill see if i can work out how to use it before I ask how :) thansk again – k3eper Sep 13 '13 at 15:40
  • Ok so I can see how to use DateAdd, but how do I get it to check every Cell down Column G and place a value in each cell down column H? – k3eper Sep 13 '13 at 15:47
  • I'm not an Excel guy, but take a look here and then post a new question if you need to fill in the gaps: http://stackoverflow.com/questions/3875415/loop-through-each-cell-in-a-range-of-cells-when-given-a-range-object – Johnny Bones Sep 13 '13 at 15:59
  • Ive taken the approach as you suggested using the code from the link 'Sub LoopRange() Dim rCell As Range Dim rRng As Range Set rRng = DDRegister.Range("G4:G6") For Each rCell In rRng.Cells Debug.Print rCell.Address, rCell.Value Next rCell End Sub' – k3eper Sep 16 '13 at 09:11
  • But as a test i used the debug code as is to see if i get any data back. all i get is an error **Object Required** I cant see what line its referring to what the issue is as all is declared and set :( – k3eper Sep 16 '13 at 09:14
0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139