Stuck on pesky If Statement that's ignoring my criteria, need help

[pawest]

Hello,

I'm analyzing data. I want the code to analyze everything from the last row up so long as the date equals the preset date. If the date doesn't equal the preset date, I want the loop to exit. The loop is not exiting for me. Here's my code:

Sub PrintItems()

Dim r As Long
Dim lastRowSummary As Long, lastRowThresholds As Long
Dim sCell As Range


tDate = Application.Workbooks("Project.xlsm").Worksheets("Main").Range("D5")

    Application.ScreenUpdating = True

    Sheets("Data-Summary").Activate
    
    lastRowSummary = Sheets("Data-Summary").Cells(Rows.Count, 16).End(xlUp).Row
    
    For r = lastRowSummary To 1 Step -1
    
        
        If Sheets("Data-Summary").Cells(r, 16).Value = tDate Then         
  
            If Sheets("Data-Summary").Cells(r, 8).Value > 1 Then
            
                Debug.Print Cells(r, 8).Address
                
                'Copy data
                Union( _
                Sheets("Data-Summary").Cells(r, 3), _
                Sheets("Data-Summary").Cells(r, 1), _
                Sheets("Data-Summary").Cells(r, 7), _
                Sheets("Data-Summary").Cells(r, 9), _
                Sheets("Data-Summary").Cells(r, 10), _
                Sheets("Data-Summary").Cells(r, 11) _
                ).Copy
                
                'Paste data
                Sheets("Thresholds").Activate
                
                lastRowThresholds = Sheets("Thresholds").Cells(Rows.Count, 2).End(xlUp).Row
                
                Set sCell = ActiveSheet.Cells(lastRowThresholds + 1, 3)
                
                sCell.PasteSpecial Paste:=xlPasteValues
                
                sCell.Offset(0, -1).Value = tDate
                
                                
            End If
        
        End If
        
    Next r

Do i have bad variables? Any suggestion on why the if statement that checks the tDate variable doesn't quit when the date is wrong?

Thanks! TGIF!

 

[mvptomlinson]

I'd consider checking the EXACT values of the referenced "tDate" cell and the dates in the cells being tested against. Is the tDate cell text, or an actual date value? Are the summary dates text or date values? Do any of them have a time component? If you write a formula in a cell comparing the tDate cell against a cell that should return TRUE, is that the result you get?

Also, declare ALL of your variables. I don't see tDate being declared at all.

 

[pawest]

Good suggestion. I set my tDate cell = to the range of date cells. I had to work around with the formats and now it works. thanks for your input.

I didn't declare the tDate variable yet... should it be a date, range, or other variable in your opinion?

 

[Legacy 221039]

This works for me (although it may not achieve the desired objective):

Sub PrintItems()


Dim r As Long
Dim lastRowSummary As Long, lastRowThresholds As Long
Dim sCell As Range




tDate = Application.Workbooks("Project.xlsm").Worksheets("Main").Range("D5")


    Application.ScreenUpdating = True


    Sheets("Data-Summary").Activate
    
    lastRowSummary = Sheets("Data-Summary").Cells(Rows.Count, 16).End(xlUp).Row
    
    For r = lastRowSummary To 1 Step -1
    
        
        If Sheets("Data-Summary").Cells(r, 16).Value = tDate And Sheets("Data-Summary").Cells(r, 8).Value > 1 Then
        
                Debug.Print Cells(r, 8).Address
                
                'Copy data
                Union( _
                Sheets("Data-Summary").Cells(r, 3), _
                Sheets("Data-Summary").Cells(r, 1), _
                Sheets("Data-Summary").Cells(r, 7), _
                Sheets("Data-Summary").Cells(r, 9), _
                Sheets("Data-Summary").Cells(r, 10), _
                Sheets("Data-Summary").Cells(r, 11) _
                ).Copy
                
                'Paste data
                Sheets("Thresholds").Activate
                
                lastRowThresholds = Sheets("Thresholds").Cells(Rows.Count, 2).End(xlUp).Row
                
                Set sCell = ActiveSheet.Cells(lastRowThresholds + 1, 3)
                
                sCell.PasteSpecial Paste:=xlPasteValues
                
                sCell.Offset(0, -1).Value = tDate
                
            Else
            
            x = x + 1
            
                
                                
            End If
            
        
    Next r
    
Application.CutCopyMode = False


    
Worksheets("Thresholds").Activate


MsgBox x & " instance(s) of rows not matching tdate"






End Sub