Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Error in code.  Trying to fix..

Posted on 2014-01-09
2
Medium Priority
?
266 Views
Last Modified: 2014-01-09
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

0
Comment
Question by:RWayneH
2 Comments
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39768688
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
 

Author Closing Comment

by:RWayneH
ID: 39768762
Thanks, I was stepping through it and parts did not look right... -R-
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question