Counting number of visible filttered rows if it meets the condition

[qarbiq]

I really need help, someone please help me new to VBA

So my user intrface asks user to select start and end date based on that my DATA sheet is updated and only shows those within the range , now i have another column in that filltered range and i need to count total number of DATES that are between selected dates , so basically counting total number of visible rows that meets the condition , i am having hard time can someone look at my code and let me know what i do wrong ?? here is my code

Dim ocell As Range
Dim iCnt As Long
For i1 = 2 To Data.Range("J:J").Cells.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).SpecialCells(xlCellTypeLastCell).Row
    Set ocell = Cells(i1, 5000)
    If ((ocell.Value >= lngStart And ocell.Value <= lngEnd)) Then ' please note lngStart and lngEnd are those asking from the user
        iCnt = iCnt + 1
    End If
Next
TotalNumberOfAccepted = iCnt

 

[sdday81]

What's actually happening now when you run it? Have you tried debugging your current code? When I get stuck and something isn't giving me the proper results it helps to try and step through the code line by line, and seeing what values are placed in my variables, etc... and then I will usually notice what I did wrong.

To debug you first need to set a break point on a line of code just before the problem area. You do this by clicking in the column to the left of your code where you want it to break. You have to set a break point on an expression or statement not a variable declaration. See image below... Once setting the break point you will see a red dot next to the line where it will stop when you run your program.

Now go to View Menu and choose to open "Watch Window". Once the watch window is opened up you will see it at the bottom of the VB Editor. Now you can highlight a variable or expression that evaluates to a value and drag and drop it in the watch window. When you run your program now and step through the code the watch window will show you the value that's in the variable or the value the expression equates to as you step through the code one line at a time.

Now that the watch window is open and you have a break point set. Run your code by pressing F5 when your program goes to execute the line of code where the break point is set it will stop on that line (the program is still running, just temporarily paused, so to speak). Now if you press the F8 key it will step down to the next line of code and if any values that are in your watch window were just executed on the previous line you will see their values in the window. You can either stop if you've discovered that the value was wrong and try to figure out why the value was incorrect or continue to the next line of code by pressing F8 again. If you have multiple break points set, pressing F5 will execute all the code from one break point to the next allowing you to skip from section to section quickly with out having to step into your code line by line.

Please try debugging your code and setting some variables in the watch window and see what those values equate to. If I were you, I would set my break point on the line where your for loop starts. Then I would select the below variables/expressions and drag them to the watch window, run the program F5, then step line by line F8 and watch what values come back as you step through.


Data.Range("J:J").Cells.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).SpecialCells(xlCellTypeLastCell).Row (highlight and drag this to the watch window)
i1 (highlight and drag your loop counter to watch window)
ocell.Value (highlight and drag your ocell.Value to the window)

Do the same for the below variables (I think you get the idea now )
lngStart
lngEnd
iCnt

Try this out and see if you can't figure out what's going wrong. If you're still having issues, explain to us what you discovered when debugging and what values you found
to see if we can't help you figure out what's wrong. Debugging is an important skill for any programmer/scripter.