Really Strange 1004 Application-Defined Error! 6

[kjv1611]

First off, I have used this module on many worksheets with no problems. But for some weird reason, it's going wacko on the current worksheet I was working on! For the life of me, I can see why it might have a problem, but yet how or why it got to that point is WELL beyond me! It may just be that I've overlooked something in my own code or design.

First, here is the code:

Private Sub TrimCells()
'This Macro is used to "clean up" workbooks in that it removes leading and trailing spaces in cells.
'Doing this helps to make sure that formulas compare values between cells correctly when comparing is necessary.

'*****I need at some time to add in code for finding the last portion of text on the workbook, and therefore,
'*****force the area of review to be correct dynamically.  That way, I do not have to edit the code each time
'*****in order for the row and column counts to be accurate.

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim x As Long 'Row
    Dim y As Long 'Column
On Error GoTo ErrHandle
    Set wb = ActiveWorkbook
    Application.ScreenUpdating = False
    For Each ws In wb.Worksheets
        If InStr(ws.Name, "Summary") Then
        ElseIf ws.Name = "SQL" Or ws.Name = "AccessVBA" Then
        Else
            ws.Activate
            x = 1
            y = 1
            For y = 1 To 1500
                For x = 1 To 150
                    ActiveSheet.Cells(x, y).Activate
                        If Len(ActiveCell.Formula) > 0 And Len(ActiveCell.Formula) < 5 Then
'                    If InStr(ActiveCell.Formula, "yes") Or InStr(ActiveCell.Formula, "Yes") Or InStr(ActiveCell.Formula, "yes") Or InStr(ActiveCell.Formula, "no") Then
                            ActiveCell.Formula = Trim(ActiveCell.Formula)
'                    End If
                        End If
                Next x
            Next y
        End If
    Next ws
    Set ws = Nothing
    Set wb = Nothing
    Application.ScreenUpdating = True

Exit Sub

ErrHandle:
    If Err.Number = 1004 Then
        Debug.Print "1004 Error at Cell: " & ActiveCell.Address
        Debug.Print "                  x = " & x & "     y = " & y
        Resume Next
    Else
        MsgBox "A Non-1004 Error has occurred in cell " & ActiveCell.Address & "!" & Chr(13) & Chr(13) & _
                Err.Number & " " & Err.Description & " " & Chr(13) & _
                "__________________________" & Chr(13) & _
                Err.HelpContext, vbCritical, "Non-1004 (Not Common) Error Has Occurred"
                
        Set ws = Nothing
        Set wb = Nothing
        Application.ScreenUpdating = True
    End If

End Sub

And here is the Debug.Print Output I got before I just broke into the execution, as it appeared it would never end:

1004 Error at Cell: $IV$150
                  x = 70     y = 1443
1004 Error at Cell: $IV$150
                  x = 71     y = 1443
1004 Error at Cell: $IV$150
                  x = 72     y = 1443
1004 Error at Cell: $IV$150
                  x = 73     y = 1443
1004 Error at Cell: $IV$150
                  x = 74     y = 1443
1004 Error at Cell: $IV$150
                  x = 75     y = 1443
1004 Error at Cell: $IV$150
                  x = 76     y = 1443
1004 Error at Cell: $IV$150
                  x = 77     y = 1443
1004 Error at Cell: $IV$150
                  x = 78     y = 1443
1004 Error at Cell: $IV$150
                  x = 79     y = 1443
1004 Error at Cell: $IV$150
                  x = 80     y = 1443
1004 Error at Cell: $IV$150
                  x = 81     y = 1443
1004 Error at Cell: $IV$150
                  x = 82     y = 1443
1004 Error at Cell: $IV$150
                  x = 83     y = 1443
1004 Error at Cell: $IV$150
                  x = 84     y = 1443
1004 Error at Cell: $IV$150
                  x = 85     y = 1443
1004 Error at Cell: $IV$150
                  x = 86     y = 1443
1004 Error at Cell: $IV$150
                  x = 87     y = 1443
1004 Error at Cell: $IV$150
                  x = 88     y = 1443
1004 Error at Cell: $IV$150
                  x = 89     y = 1443
1004 Error at Cell: $IV$150
                  x = 90     y = 1443
1004 Error at Cell: $IV$150
                  x = 91     y = 1443
1004 Error at Cell: $IV$150
                  x = 92     y = 1443
1004 Error at Cell: $IV$150
                  x = 93     y = 1443
1004 Error at Cell: $IV$150
                  x = 94     y = 1443
1004 Error at Cell: $IV$150
                  x = 95     y = 1443
1004 Error at Cell: $IV$150
                  x = 96     y = 1443
1004 Error at Cell: $IV$150
                  x = 97     y = 1443
1004 Error at Cell: $IV$150
                  x = 98     y = 1443
1004 Error at Cell: $IV$150
                  x = 99     y = 1443
1004 Error at Cell: $IV$150
                  x = 100     y = 1443
1004 Error at Cell: $IV$150
                  x = 101     y = 1443
1004 Error at Cell: $IV$150
                  x = 102     y = 1443
1004 Error at Cell: $IV$150
                  x = 103     y = 1443
1004 Error at Cell: $IV$150
                  x = 104     y = 1443
1004 Error at Cell: $IV$150
                  x = 105     y = 1443
1004 Error at Cell: $IV$150
                  x = 106     y = 1443
1004 Error at Cell: $IV$150
                  x = 107     y = 1443
1004 Error at Cell: $IV$150
                  x = 108     y = 1443
1004 Error at Cell: $IV$150
                  x = 109     y = 1443
1004 Error at Cell: $IV$150
                  x = 110     y = 1443
1004 Error at Cell: $IV$150
                  x = 111     y = 1443
1004 Error at Cell: $IV$150
                  x = 112     y = 1443
1004 Error at Cell: $IV$150
                  x = 113     y = 1443
1004 Error at Cell: $IV$150
                  x = 114     y = 1443
1004 Error at Cell: $IV$150
                  x = 115     y = 1443
1004 Error at Cell: $IV$150
                  x = 116     y = 1443
1004 Error at Cell: $IV$150
                  x = 117     y = 1443
1004 Error at Cell: $IV$150
                  x = 118     y = 1443
1004 Error at Cell: $IV$150
                  x = 119     y = 1443
1004 Error at Cell: $IV$150
                  x = 120     y = 1443
1004 Error at Cell: $IV$150
                  x = 121     y = 1443
1004 Error at Cell: $IV$150
                  x = 122     y = 1443
1004 Error at Cell: $IV$150
                  x = 123     y = 1443
1004 Error at Cell: $IV$150
                  x = 124     y = 1443
1004 Error at Cell: $IV$150
                  x = 125     y = 1443
1004 Error at Cell: $IV$150
                  x = 126     y = 1443
1004 Error at Cell: $IV$150
                  x = 127     y = 1443
1004 Error at Cell: $IV$150
                  x = 128     y = 1443
1004 Error at Cell: $IV$150
                  x = 129     y = 1443
1004 Error at Cell: $IV$150
                  x = 130     y = 1443
1004 Error at Cell: $IV$150
                  x = 131     y = 1443
1004 Error at Cell: $IV$150
                  x = 132     y = 1443
1004 Error at Cell: $IV$150
                  x = 133     y = 1443
1004 Error at Cell: $IV$150
                  x = 134     y = 1443
1004 Error at Cell: $IV$150
                  x = 135     y = 1443
1004 Error at Cell: $IV$150
                  x = 136     y = 1443
1004 Error at Cell: $IV$150
                  x = 137     y = 1443
1004 Error at Cell: $IV$150
                  x = 138     y = 1443
1004 Error at Cell: $IV$150
                  x = 139     y = 1443
1004 Error at Cell: $IV$150
                  x = 140     y = 1443
1004 Error at Cell: $IV$150
                  x = 141     y = 1443
1004 Error at Cell: $IV$150
                  x = 142     y = 1443
1004 Error at Cell: $IV$150
                  x = 143     y = 1443
1004 Error at Cell: $IV$150
                  x = 144     y = 1443
1004 Error at Cell: $IV$150
                  x = 145     y = 1443
1004 Error at Cell: $IV$150
                  x = 146     y = 1443
1004 Error at Cell: $IV$150
                  x = 147     y = 1443
1004 Error at Cell: $IV$150
                  x = 148     y = 1443
1004 Error at Cell: $IV$150
                  x = 149     y = 1443
1004 Error at Cell: $IV$150
                  x = 150     y = 1443
1004 Error at Cell: $IV$150
                  x = 1     y = 1444
1004 Error at Cell: $IV$150
                  x = 2     y = 1444
1004 Error at Cell: $IV$150
                  x = 3     y = 1444
1004 Error at Cell: $IV$150
                  x = 4     y = 1444
1004 Error at Cell: $IV$150
                  x = 5     y = 1444
1004 Error at Cell: $IV$150
                  x = 6     y = 1444
1004 Error at Cell: $IV$150
                  x = 7     y = 1444
1004 Error at Cell: $IV$150
                  x = 8     y = 1444
1004 Error at Cell: $IV$150
                  x = 9     y = 1444
1004 Error at Cell: $IV$150
                  x = 10     y = 1444
1004 Error at Cell: $IV$150
                  x = 11     y = 1444
1004 Error at Cell: $IV$150
                  x = 12     y = 1444
1004 Error at Cell: $IV$150
                  x = 13     y = 1444
1004 Error at Cell: $IV$150
                  x = 14     y = 1444
1004 Error at Cell: $IV$150
                  x = 15     y = 1444
1004 Error at Cell: $IV$150
                  x = 16     y = 1444
1004 Error at Cell: $IV$150
                  x = 17     y = 1444
1004 Error at Cell: $IV$150
                  x = 18     y = 1444

And actually, that wasn't all of the debug.print info, because apparently, there is a limit to the Immediate window text, so it just overwrites itself after a while.

This is the VERY FIRST instance of the 1004 error (debug.print output):

1004 Error at Cell: $IV$150
                  x = 1     y = 257

A separate question I'd like to pose is this: is it possible to edit the VBA settings so that there is no limit or else a much higher limit than the default settings allow?

Any advice on how I got to the error, and how to correct this would be GREATLY appreciated.



--

"If to err is human, then I must be some kind of human!" -Me

 

[combo]

Change order: ...Cells(y, x) (Cells(RowIndex,ColumnIndex).
Instead of Immediate window use text file - in practice unlimited.

combo

 

[kjv1611]

And I should have included this information:
I am using MS Excel 2003.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

How do I write to or create a text file? That would be most beneficial at times.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Also, why would I need to change the row,column order? I have it set currently to Cells(x,y) which is Cells(row,column)

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim x As Long 'Row
    Dim y As Long 'Column

And the loops go:

 For y = 1 To 1500
    For x = 1 To 150

I must be missing something somewhere. Can you point out the line of code that is causing the problem?

--

"If to err is human, then I must be some kind of human!" -Me

 

[JerryKlmns]

>How do I write to or create a text file?
From yesterday thread705-1426794

 

[zahead31]

Dim y as Long 'Column

For y = 1 To 1500

1004 Error at Cell: $IV$150
x = 1 y = 257

You may have your x and y reversed on your For loops. You are probably getting that error at y=257 because there are only 256 columns.

Hope this helps,
Matt

 

[kjv1611]

Thanks for the link to the other thread about writing to a text file. How convenient is that?!

--

"If to err is human, then I must be some kind of human!" -Me

 

[rmikesmith]

As Combo indicated, you're row/column references appear to be reversed. In any case, Excel 2003 (and earlier) has a limit of 256 columns, so if you are allowing y to increment to 1500 you're going to throw an error.


Regards,
Mike

 

[kjv1611]

Okay, so somehow, some way, VBA is swapping my variables in the background perhaps????

For [BLUE][B]y[/B][/BLUE] = 1 To 1500
   For [HIGHLIGHT][B]x[/B][/HIGHLIGHT] = 1 To 150
      ActiveSheet.Cells([HIGHLIGHT][B]x[/B][/HIGHLIGHT], [BLUE][B]y[/B][/BLUE]).Activate

Am I just REALLY missing something? I mean, I have x in the row position, and y in the column position for sure, assuming this is correct:

[highlight white]ActiveSheet.Cells(Row,Column)[/highlight]

Are my eyes just playing tricks on me? How in the world did I get the x(row)/y(column) arrangement switched if that's the case? Something that should be so simple, and I've done it a million (well not really a million) times...

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

In your code y is a column number, so why this ?
For y = 1 To 1500

You can't have 1500 columns !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

y increments from 1 to 1500. y is your column counter. There are only 256 columns therefore when y > 256 = error

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kjv1611]

OH.........
MY.........
GOODNESS.

Okay, I is really dumb.

I have to admit this, though I don't want to. Oh boy, this is scary. And now I see it RIGHT after I ate lunch. I guess I was hungry, and didn't know it.

I had "Row" and "Column" mixed up in my brain. Oh boy, that is really scary.

Thanks for all the help, guys. Um, I think I'm going to go and hide in a corner, now.



--

"If to err is human, then I must be some kind of human!" -Me

 

[xlbo]

Don't even give it another thought. If you only knew how many times I have been completely unable to see my own errors until a colleague points out the all too obvious misplaced comma, hyphen, space, you name it. Happens to all of us

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[zahead31]

Hey, that is what your fellow Tek-Tippers are here for, to be that second set of eyes!!!
I agree with xlbo... it happens to everyone. Couldn't tell you how many times I spent stumped, and it just took the once over of a fresh set of eyes to find the simple mistake.