Excel Help Forum - View Single Post - Changing Date Values to a working Integer

broro183 · #4 06-30-2009, 06:24 PM

hi,

edit: my post was made before I saw JB's post or the OP's subsequent reply with link to the other thread. This is why I fleshed out my suggested code as a "made up" example.
end edit.

Current day date values are larger than the allowable limits of the Integer Datatype (-32,768 to 32,767, see the Help files or http://www.vbtutor.net/lesson5.html) & this fundamental problem with your aim is highlighted in your title. The value of today's date can be seen in a spreadsheet by entering "=VALUE(TODAY())" which evaluates 39,994 and this is > 32,767. To overcome this, try changing any declarations to the Long datatype.

I have also made a few changes to your code but haven't tested it - hopefully it works as required (with any necessary changes as per my comments)...

Code:

Option Explicit
Sub Test()
    Dim OutIY As Worksheet
    Dim AnotherSht As Worksheet
    Dim findit As Range
    Dim i As Long

    'change as needed
    Set OutIY = Worksheets("Output")
    Set WhichSht = Worksheets("Which Sheet should this be")
    Set findit = AnotherSht.Range("a1")

    ''was originally...
    'If OutIY.Cells(i, "D") <> OutIY.Cells(i, "F") And WorksheetFunction.Int(Cells(i, "G").Value = WorksheetFunction.Int(today).Value) Then Cells(findit.Row, "G") = (Format(Now, "mm/dd/yyyy"))
    'try...
    If (OutIY.Cells(i, "D").Value2 <> OutIY.Cells(i, "F").Value2) And (WhichSht.Cells(i, "G").Value2 = CLng(Date)) Then
        With WhichSht.Cells(findit.Row, "G")
            .Value2 = CLng(Date)
            .NumberFormat = "mm/dd/yyyy"
        End With
    Else
    'do nothing
    End If
        'release variables
        Set OutIY = Nothing
        Set AnotherSht = Nothing
        Set findit = Nothing
    End Sub