Error in code. Trying to fix..

Around line, 26 thru 30 it is changing the value all to zero instead of dates which is effecting the final output (bigtime).  Can someone look at this and advise to a possible issue with this precedure?  Its original Question number is the name of the Sub.. referencing the other question may help gather some of the background info on it.  Please advise and thanks. -R-

Option Explicit

Public Sub Q_28325796(Optional parmDate)
    Dim dtToday As Date
    Dim wks As Worksheet
    Dim rng As Range
    Dim lngLoop As Long
    Dim lngEndRow As Long
    Dim lngStartRow As Long
    
    Set wks = ActiveSheet
    If IsMissing(parmDate) Then
        dtToday = Date
    Else
        dtToday = parmDate
    End If
    'delete the hyphen row
    Set rng = wks.Range("A1").End(xlDown)
    If rng.Text Like "-*" Then
        rng.EntireRow.Delete
        Set rng = wks.UsedRange
    End If
    Application.ScreenUpdating = False
    
    'Insert a new column beside AG and create a header
    wks.Columns(34).Insert
    wks.Cells(1, 34).Value = "DateValue"
    'populate the new column with date values from column AG
    wks.Range(wks.Cells(2, 34), wks.Cells(wks.Cells.SpecialCells(xlCellTypeLastCell).Row, 34)).FormulaR1C1 = "=N(rc[-1])"
    'sort by the new date value column
    wks.Range("A1").CurrentRegion.Sort key1:=wks.Cells(1, 34), Header:=xlYes
    
    'iterate the AH cells from bottom to top, deleting the rows
    'with dates <> yesterday or today
    lngEndRow = -1
    lngStartRow = lngEndRow + 1
    lngLoop = wks.Range("AH2").End(xlDown).Row
    For lngLoop = lngLoop To 2 Step -1
        Select Case wks.Cells(lngLoop, 34).Value
            Case dtToday - 1 To dtToday, 0
                lngStartRow = lngLoop + 1
                If lngStartRow <= lngEndRow Then
                    wks.Range(wks.Rows(lngStartRow), wks.Rows(lngEndRow)).Delete
                    Application.StatusBar = "Deleted rows: " & lngStartRow & " to " & lngEndRow
                End If
                lngEndRow = -1
            Case Else
                If lngEndRow = -1 Then
                    lngEndRow = lngLoop
                End If
        End Select
    Next
    'delete the datevalue column
    wks.Columns(34).Delete
    'sort by column 1
    wks.Range("A1").CurrentRegion.Sort key1:=wks.Cells(1, 1), Header:=xlYes
    
    Application.ScreenUpdating = True
    ActiveSheet.UsedRange.AutoFilter Field:=33, Criteria1:="=" & dtToday - 1, Operator:=xlOr, Criteria2:="=" & dtToday
    Application.StatusBar = vbNullString
End Sub

Open in new window

RWayneHAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

It seems tha the Dates are not formatted as Date but as Text.
That's why the function N() gives 0 back.

Regards
0
 
RWayneHAuthor Commented:
Thanks, I was stepping through it and parts did not look right... -R-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.