Solved

Error in code.  Trying to fix..

Posted on 2014-01-09
2
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 500 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

632 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