mardi 24 mai 2016

Break from for loop back into if statement vba

I am trying to perform an action that will see if the date in range (dateRng) is less than today's date, and then, if it is, perform the for loop to hide the rows w.here a value in the column is zero. (I am paying off loans, and every month I want it to hide any loans that have been paid off.) Months are across columns, loans are in rows. Loan balance is (i, j).

The problem is that it never exits the for loop to go back and check the date after every new 'j' (column). It just stays in the for loop. I have tried break, exit, continue, etc. None seem to work, at least where I place them. How do I get it to check for the date, compare to 'today', THEN run the for loop to check each cell in the column, before moving on to column 2, checking the date and performing the same for loop.

It would be good to have it be dynamic, but that is not necessary, as every month I could just change the ranges in the code. This is strictly for my personal use. Any help is appreciated. thank you.

Sub hidePaid()

Dim day As Range, loanRng As Range, loanSum As Worksheet, dateRng As Range, cel2 As Range, i As Long, j As Long, col As Range

Set loanSum = ThisWorkbook.Worksheets("Loan Sum")
loanSum.Activate

Set dateRng = ActiveSheet.Range("D2:R2")
Set loanRng = ActiveSheet.Range("D4:R16")

For Each day In dateRng

If day.Value < Date Then

    For j = 1 To loanRng.Columns.Count
    For i = 1 To loanRng.Rows.Count

        If loanRng.Cells(i, j).Value < 1 Then
               loanRng.Cells(i, j).EntireRow.Hidden = True
        End If

    Next i
    Next j



End If

Next
End sub

Aucun commentaire:

Enregistrer un commentaire