Problem with a loop statement

[328mike]

Hey all,

I'm trying to use the cells command for a computation but it's not working when I try to use the counting variable by itself as the row index.

Month(Cells(k, 36)) < ETCMonth

If I change it to Cells(1 + k, 36) it works although the answers are incorrect that it gives. Is their a different way I can write this?

Here is the loop:

Dim k As Long, LR As Long
With Sheets("Ready for Submission")
    LR = .Range("Z" & Rows.Count).End(xlUp).Row
    For k = 1 To LR
        If Range("Z" & k).Value = "CLOSED" And Month(Cells(k, 36)) < ETCMonth Then .Rows(k).Cut Destination:=Sheets("Closed Accounts").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[wigi]

Hello

What is the contents of ETCMonth? And the contents of column AJ?

Most probably you also need to add the . in front of all ranges in your IF statement.

 

[Tom Urtis]

...it works although the answers are incorrect that it gives.

Well then I'd say it really doesn't work.


But two questions...

(1)
using k as the variable for both a month and a row means you don't have, and don't expect to ever have, more than 12 rows of data. I doubt that is the case.

(2)
Why loop when you can filter the table, cut visible cells' rows, paste the data where you want it, and delete the filtered range's visible (just-cut) rows? It would run faster to filter unless you really do have only 12 rows you'd be looping through.

 

[328mike]

'Processes date information again if it is entered incorrectly
Dim ErrCount As Long
On Error GoTo ErrHandler
ETCMonth = Month(DataMonth)
    DataMonthFormatted = Format(DataMonth, "Yyyy-Mm")
Dim j As Long
For j = 1 To DataRange
    Cells(1 + j, 1).Formula = DataMonthFormatted
If Month(Cells(1 + j, 5)) = ETCMonth Then
    Cells(1 + j, 10).Value = "Y"
    End If
    Next j

'Formatting header
Rows("1:1").Select
    Selection.Copy
    Sheets("Closed Accounts").Select
    ActiveSheet.Paste
    Range("A3").Select
    ActiveWindow.FreezePanes = True
    Sheets("Ready for Submission").Select
    Range("A3").Select

'Begin account filtering code
Dim k As Long, LR As Long
With Sheets("Ready for Submission")
    LR = .Range("Z" & Rows.Count).End(xlUp).Row
    For k = 1 To LR
        If Range("Z" & k).Value = "CLOSED" And Month(Cells(0 + k, 36)) < ETCMonth Then .Rows(k).Cut Destination:=Sheets("Closed Accounts").Range("A" & Rows.Count).End(xlUp).Offset(1)
'        If Range("Q" & k).Value = "" Then .Rows(k).Cut Destination:=Sheets("Missing Information").Range("A" & Rows.Count).End(xlUp).Offset(1)
'        If Range("R" & k).Value = "" Then .Rows(k).Cut Destination:=Sheets("Missing Information").Range("A" & Rows.Count).End(xlUp).Offset(1)
'        If Range("R" & k).Value = "," Then .Rows(k).Cut Destination:=Sheets("Missing Information").Range("A" & Rows.Count).End(xlUp).Offset(1)
'        If Range("S" & k).Value = "" Then .Rows(k).Cut Destination:=Sheets("Missing Information").Range("A" & Rows.Count).End(xlUp).Offset(1)
'        If Range("U" & k).Value = "" Then .Rows(k).Cut Destination:=Sheets("Missing Information").Range("A" & Rows.Count).End(xlUp).Offset(1)
'        If Range("V" & k).Value = "" Or Len(Range("V" & k).Value) < 5 Then .Rows(k).Cut Destination:=Sheets("Missing Information").Range("A" & Rows.Count).End(xlUp).Offset(1)
       Next k
    On Error Resume Next
    .Range("Z1:Z" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
End With

The commented stuff was used in another file and all works fine. I basically need to keep any status date that is the same month as the data month for further processing that I will run in a different loop. Anything with a status date before the data month is irrelevant for my reporting purposes and can be deleted from the list and ignored.

 

[328mike]

Ok so now I can get the loop to run but their is some problem with comparing the two variables.

In the line:

If Range("Z" & k).Value = "CLOSED" And Range("AJ" & k).Value < DataMonth Then .Rows(k).Cut Destination:=Sheets("Closed and Pending Accounts").Range("A" & Rows.Count).End(xlUp).Offset(1)

It will read the variable in the cell which will produce something like this:

7/7/2011 5:05:44 PM

and then try to compare it to the variable I have the user input called DataMonth which usually appears like this:

04/01/2011

or any acceptable date format.

Do I have some sort of variable problem? As it stands the program is deleting all closed accounts with any status date because it says all of them are less than the DataMonth variable. If I change the operand to greater than then none of them are deleted.

What is the problem here??

 

[Jonmo1]

I think you need to properly reference the sheet that you declared in the With statement. ON ALL ranges and Cells

If .Range("Z" & k).Value = "CLOSED" And .Range("AJ" & k).Value < DataMonth Then .Rows(k).Cut Destination:=Sheets("Closed and Pending Accounts").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[328mike]

Didn't make a difference. I really can't figure out how their would be a type mismatch here.

 

[328mike]

Ok so the problem lies within the DataMonth variable.

I hard coded in a date to test in the if statement and it worked perfectly. As a reminder, my DataMonth variable is a value from an input box and is collected here:

Dim DataMonth
Dim DataMonthFormatted
Dim ETCMonth
Dim Answer As Integer

DataMonth = Application.InputBox("Please enter a data month. (Mm/01/Yyyy)")

 

[Jonmo1]

You can configure the inputbox to only accept valid dates

Dim datamonth As Date
datamonth = Application.InputBox("Please enter a data month. (Mm/01/Yyyy)", , , , , , , 1)


Also, to help user's see the correct date format, you can include Today's date as the default entry..

datamonth = Application.InputBox("Please enter a data month. (Mm/01/Yyyy)", , Date, , , , , 1)


Hope that helps.

 

[328mike]

Ok once again I have solved my own problem I was having some sort of issue with processing the text as a date stored in the DataMonth variable.

If Range("Z" & k).Value = "CLOSED" And .Range("AJ" & k).Value < CDate(DataMonth) Then .Rows(k).Cut Destination:=Sheets("Closed and Pending Accounts").Range("A" & Rows.Count).End(xlUp).Offset(1)

CDate() was the golden ticket