Listview Loading Data w/Criteria

JarekM

Board Regular
Joined
Nov 13, 2018
Messages
86
Hi.

I have a problem. In the code provided below I have three conditions where if I were to have a value of 0 then it won't show up on the listview, but it still shows up regardless. The first two conditions work finely, but the third condition doesn't. The third condition is based on the value zero on column 14, it isn't suppose to load the data with the 0 value. There is also an image that is provided in the link the will show the sheet.
Can anyone fix this problem, and explain to me what I am doing incorrectly. Thanks to everyone.
Code:
Code:
    With ListView2
        .HideColumnHeaders = True
        .Appearance = ccFlat
        .ColumnHeaders.Clear
        .View = lvwReport
        .FullRowSelect = False
        .HideSelection = True
        .Gridlines = True
    
        .ColumnHeaders.Add , , "Due Since", 110
        .ColumnHeaders.Add , , "Clients-Nr", 72, Alignment:=1
        .ColumnHeaders.Add , , "Clients-Nr", 0, Alignment:=1
    End With
    
        ' Adds data
        ListView2.ListItems.Clear
        row = 4
    
        Do Until Sheets("Payments_DB").Cells(row, 5) = ""
    
            If (Sheets("Payments_DB").Cells(row, 5) <= Date + 5) _
                            And Sheets("Payments_DB").Cells(row, 14) > "0.00" _
                            And (Sheets("Payments_DB").Cells(row, 5) >= Date - 5) _
                            Or (Sheets("Payments_DB").Cells(row, 5) < Date) Then
            
                Set Li = ListView2.ListItems.Add(Text:=Format(Sheet11.Cells(row, "A").Value, "General"))   'Property Address
                            Li.ListSubItems.Add Text:=Format(Sheet11.Cells(row, "N").Value, "#,##0.00")
                            Li.ListSubItems.Add Text:=Format(Sheet11.Cells(row, "E").Value, "m/d/yyyy")
            End If
            row = row + 1
        Loop

Image:
https://imgur.com/a/S4XIsV2
https://imgur.com/a/LXnskG8

Thank you
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try

Code:
If [B][COLOR=#ff0000]([/COLOR][/B]Sheets("Payments_DB").Cells(Row, 5) <= Date + 5 _
          And Sheets("Payments_DB").Cells(Row, 14) >[B][COLOR=#ff0000] 0[/COLOR][/B] _
          And Sheets("Payments_DB").Cells(Row, 5) >= Date - 5[B][COLOR=#ff0000])[/COLOR][/B] _
          Or[COLOR=#0000ff] Sheets("Payments_DB").Cells(Row, 5) < Date[/COLOR] Then

I guess you want to check a date between a period and also be greater than zero.


Or simply if it is less than today.
 
Last edited:
Upvote 0
Thank you for the fast response.

Yes, that is what I want. The date should be between a period, 5 days after and before the initial date and to have the value greater than 0, but I did try the changes that you have provided and it didn't fix the problem. It is still loading the data with the 0 value

Thanks for the help.
 
Upvote 0
I don't understand why you have the second validation:

Code:
Or Sheets("Payments_DB").Cells(Row, 5) < Date

That loads everything that is less than today's date.

Or you remove it to have this:

Code:
If (Sheets("Payments_DB").Cells(Row, 5) <= Date + 5 _
          And Sheets("Payments_DB").Cells(Row, 14) > 0 _
          And Sheets("Payments_DB").Cells(Row, 5) >= Date - 5) _
          Then

Or add the validation of 0

Code:
If [COLOR=#008000](Sheets("Payments_DB").Cells(Row, 5) <= Date + 5 _[/COLOR]
[COLOR=#008000]          And Sheets("Payments_DB").Cells(Row, 14) > 0 _[/COLOR]
[COLOR=#008000]          And Sheets("Payments_DB").Cells(Row, 5) >= Date - 5)[/COLOR] _
          [COLOR=#a52a2a]Or[/COLOR] [COLOR=#0000ff](Sheets("Payments_DB").Cells(Row, 5) < Date _[/COLOR]
[COLOR=#0000ff]          And Sheets("Payments_DB").Cells(Row, 14) > 0)[/COLOR] Then
 
Upvote 0
Thank you so much for the help.

Once I have added the validation of 0, it resolved the problem.

Thank you once again. :)
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top