Date within a year from now

[tomleitch]

Hi all,

I've had a search but I can't really find what I'm trying to do.....

I'm running through cells and I want to only use cells that have a date within the next year


The code I have is this:

With CreateObject("scripting.dictionary") 


      For Each cl In Ws2.Range("A10", Ws2.Range("A" & Rows.Count).End(xlUp))
      If Cells(cl.Row, PLANWST).Value < (Date + 365) And Not .exists(cl.Value) Then .Add cl.Value, Array(cl.Offset(, 1).Value, cl.Offset(, 3).Value, cl.Offset(, 4).Value, cl.Offset(, 5).Value, cl.Offset(, 6).Value, cl.Offset(, 7).Value, cl.Offset(, 8).Value, cl.Offset(, 9).Value, cl.Offset(, 10).Value)
      Next cl

(PLANWST is the column reference of my date column)

The code doesn't seem to work and I think it's this bit that's wrong:

If Cells(cl.Row, PLANWST).Value < (Date + 365)

Any help appreciated

Thanks
Tom

 

[MoshiM]

Hi all,

I've had a search but I can't really find what I'm trying to do.....

I'm running through cells and I want to only use cells that have a date within the next year


The code I have is this:

With CreateObject("scripting.dictionary") 


      For Each cl In Ws2.Range("A10", Ws2.Range("A" & Rows.Count).End(xlUp))
      If Cells(cl.Row, PLANWST).Value < (Date + 365) And Not .exists(cl.Value) Then .Add cl.Value, Array(cl.Offset(, 1).Value, cl.Offset(, 3).Value, cl.Offset(, 4).Value, cl.Offset(, 5).Value, cl.Offset(, 6).Value, cl.Offset(, 7).Value, cl.Offset(, 8).Value, cl.Offset(, 9).Value, cl.Offset(, 10).Value)
      Next cl

(PLANWST is the column reference of my date column)

The code doesn't seem to work and I think it's this bit that's wrong:

If Cells(cl.Row, PLANWST).Value < (Date + 365)

Any help appreciated

Thanks
Tom

https://docs.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/dictionary-object

 

[tomleitch]

https://docs.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/dictionary-object

Thanks for the pointer MoshiM, but I'm still not sure exactly where I'm going wrong... I had a look at the article but I'm still not any the wiser.

I'm trying to tell it that if the date is more than a year in the future then not to include the item in the dictionary.

The rest of it is working fine, if I just take the bit out thats trying to check the date - so I know that the rest of my code is ok.

Thanks
Tom

 

[MoshiM]

Thanks for the pointer MoshiM, but I'm still not sure exactly where I'm going wrong... I had a look at the article but I'm still not any the wiser.

I'm trying to tell it that if the date is more than a year in the future then not to include the item in the dictionary.

The rest of it is working fine, if I just take the bit out thats trying to check the date - so I know that the rest of my code is ok.

Thanks
Tom

Try

Dim D as dictionary
Set D=CreateObject("scripting.dictionary")


With D


      For Each cl In Ws2.Range("A10", Ws2.Range("A" & Rows.Count).End(xlUp))
      If Cells(cl.Row, PLANWST).Value < (Date + 365) And Not .exists(cl.Value) Then .Add cl.Value, Array(cl.Offset(, 1).Value, cl.Offset(, 3).Value, cl.Offset(, 4).Value, cl.Offset(, 5).Value, cl.Offset(, 6).Value, cl.Offset(, 7).Value, cl.Offset(, 8).Value, cl.Offset(, 9).Value, cl.Offset(, 10).Value) [COLOR=#333333]     
 Next cl[/COLOR]

 

[tomleitch]

Try

Dim D as dictionary
Set D=CreateObject("scripting.dictionary")


With D


      For Each cl In Ws2.Range("A10", Ws2.Range("A" & Rows.Count).End(xlUp))
      If Cells(cl.Row, PLANWST).Value < (Date + 365) And Not .exists(cl.Value) Then .Add cl.Value, Array(cl.Offset(, 1).Value, cl.Offset(, 3).Value, cl.Offset(, 4).Value, cl.Offset(, 5).Value, cl.Offset(, 6).Value, cl.Offset(, 7).Value, cl.Offset(, 8).Value, cl.Offset(, 9).Value, cl.Offset(, 10).Value) [COLOR=#333333]     
 Next cl[/COLOR]

All sorted now - thanks