Search code examples
vbaexcelexcel-2007excel-2003

Excel Run-time error 16 expression too complex

I have this basic code in an Excel sheet

For x = 1 To ThisWorkbook.Worksheets("GRP0").Range("D9:D112").Rows.Count
' do work here
Next x

though when I execute it gives the error

Run-time error '16':

Expression too complex

I'm using Excel 2007 in compatibility mode, the sheet, I believe was made for/in Excel 2003, if that helps. Any ideas as to what is causing the error?

Solution

  • Let me just answer this question and let it leave 'unanswered' section... and for others who would look for the answer and see perfectly written question.

    Problem described happens from time to time. I don't know exact reason but there is one simple way to solve it. You just need to use variable, set its value before loop starts and use this variable in loop starting line. This could be as follow:

    Dim rowsCount As Long
    rowsCount = ThisWorkbook.Worksheets("GRP0").Range("D9:D112").Rows.Count
    'and any other complex statement could be placed here
For x = 1 To rowsCount
    'do work here
Next x

    According to my experience all 'expression too complex' problems could be solved that way.