Thread: Changing Date Values to a working Integer

Hello,

I need help in making this a true working statement.
Code as is errors for "object doesn't support this property or method"

Code:

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"))

where "G" = 06/30/2009 (or any other date)
Thanks again and always,

BDB

Maybe:

Code:

....WorksheetFunction.Int(Date).Value ....

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

hth
Rob

hi BDB,

Thanks for the feedback, I'm pleased we could help

In the interests of "teaching you to fish rather than just giving you a fish" I've wandered through the code in your last post & have made a few more changes which tidy it up (ignoring the comments! ) & may even make it faster. To pass on my thoughts (note I'm still learning too & am open to being challenged) & hopefully help you learn a bit more I've included heaps of comments about my suggested changes. I've also listed the code without the extra comments for ease of use.

If you are happy with this thread & the other thread that is linked in a previous post, can you please mark them as Solved?

Code:

Option Explicit

Sub Test2WithLotsOfComments()
    Dim OutIY As Worksheet
    'if using a set template file the number of header rows should not be changed within the _
     code, therefore by defining this as a Const (ie Constant) it can not be changed during the macro by mistake.
    Const FirstDataRow As Long = 10
    Dim i As Long
    Dim LastDataRow As Long

    'to speed up the macro (you could also turn off calculations & other actions/events but I don't _
     think it's needed in this situation).
    Application.ScreenUpdating = False

    Set OutIY = ThisWorkbook.Worksheets("Sheet1")
    'can remove the "WhichSht" because this is Set to be the same as the OutIY sheet therefore it is unnecessary.

    'by preceding all the "Cells(..." with a dot ie ".Cells(...", they become sub-ordinate to the _
     OutIY sheet Object in the below With Statement.
    With OutIY

        'remove this from the loop so that it is only calculated once (& there is no chance of it being changed within in the _
         loop, eg if a row was deleted).
        LastDataRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        For i = FirstDataRow To LastDataRow

            'I think the below findit variable can be removed & replaced by the simpler use of the looping "i" variable (with _
             this belief, I've changed the remaining code to replace "findit" with "i").
            '### Using "i" assumes that a type of inventory will only appear once in the list, if this assumption is _
             inaccurate then you will need to use "findit", but NOTE, you may need to incorporate a loop to ensure that _
             all rows for the specific inventory type are checked & correctly adjusted.
            'delete this line...        Set findit = .Range("A:A").Find(what:=.Cells(i, 1).Value)

            'the code previously posted relied on the default property of the Cells property being Value, but _
             potentially this could change in a future version of Excel (unlikely but possible) therefore I _
             think it's better to always explicitly state the desired property as ".value" (see _
             brief comments @ http://www.dailydoseofexcel.com/archives/2004/07/07/the-strange-object/).
            'Also, I've used ".value2" for the date column due to potential Excel-VBA translation _
             problems demonstrated in http://support.microsoft.com/kb/182812
            If ((.Cells(i, "D").Value <> .Cells(i, "F").Value) And (.Cells(i, "G").Value2 < CLng(Date))) Then

                'move any code that doesn't need to be in the With statement outside it.
                .Cells(i, "F").Value = Cells(i, "D").Value
                'I've changed the following for speed ("in theory" but probably not actually noticeable) by moving the _
                 formatting outside the loop. therefore the below commented lines can be replaced by the _
                 subsequent uncommented line & the later formatting line identified with "'***"
                'With .Cells(i, "G")
                '    .Value2 = CLng(Date)
                '    .NumberFormat = "mm/dd/yyyy"
                'End With
            Else
                'do nothing
            End If
        Next i
        '*** added to format all dates in one go (after the loop has finished).
        .Range(.Cells(FirstDataRow, "G"), .Cells(LastDataRow, "G")).NumberFormat = "mm/dd/yyyy"
    End With

    'release variables.
    Set OutIY = Nothing
    'reset Excel settings
    Application.ScreenUpdating = True
    MsgBox "done"
End Sub

More succinctly as...

Code:

Option Explicit
Sub Test2WithFewerComments()
    Dim OutIY As Worksheet
    Const FirstDataRow As Long = 10
    Dim i As Long
    Dim LastDataRow As Long
    'to speed up the macro
    Application.ScreenUpdating = False
    Set OutIY = ThisWorkbook.Worksheets("Sheet1")
    With OutIY
        LastDataRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = FirstDataRow To LastDataRow
            If ((.Cells(i, "D").Value <> .Cells(i, "F").Value) And (.Cells(i, "G").Value2 < CLng(Date))) Then
                .Cells(i, "F").Value = Cells(i, "D").Value
            Else
                'do nothing
            End If
        Next i
        .Range(.Cells(FirstDataRow, "G"), .Cells(LastDataRow, "G")).NumberFormat = "mm/dd/yyyy"
    End With
    'release variables.
    Set OutIY = Nothing
    'reset Excel settings
    Application.ScreenUpdating = True
    MsgBox "done"
End Sub

hth
Rob