Can't use a particular variable within a nested for loop?

Question

The following code gives me an error 1004. (Note that iElements and iSortOrder() is well defined earlier in the code, and CopyHeader is a subroutine)

    Dim page As Long
    page = 0

    For i = 0 To iElements

        ' Every five elements is a new page.  Every new page, copy the header and update current page count.
        If ((i Mod 5) - 1) = 0 Then
            CopyHeader ((page * 34) + 1)
            page = page + 1
        End If

        For m = 1 To 16
            For n = 0 To 7
                Sheet2.Cells((page * 4) + (i * 7) + n, m) = Sheet1.Cells((5 + (iSortOrder(i) * 9) + n), m)
            Next n
        Next m
    Next i

If I change the single line within the nested loops to:

Sheet2.Cells((4) + (i * 7) + n, m) = Sheet1.Cells((5 + (iSortOrder(i) * 9) + n), m)

That is, I remove the reference to page in this line only, the code executes just fine.

This is baffling me. Why would referencing my "page" variable in this line not work? I thought it might be a scope issue-- like VBA doesn't allow variables defined outside of a for loop to be used in a for loop-- but "page" is utilized perfectly well earlier in the loop. So what's wrong?

In case it's not clear, the purpose of the code is to copy blocks of cells from sheet1 to sheet2 in a different order, stripping out some blank lines in the process (9 rows going down to 7 rows). Sheet1 has a single header at the beginning (the "5+"), Sheet2 needs a header on every page (the "(page*4)+").

Renaming to "pg" changes nothing. Renaming to "foo" changes nothing. Same situation despite variable name. — RichardW
– RichardW, Commented May 30, 2014 at 19:24
It doesn't even "compile", or whatever VBA does instead of compiling. page (or pagecount, as I've now renamed it) starts at 1 (after the first IF statement, which triggers for the first row) and gets up to 47 (if I take pagecount out of that one line). Sheet1 has 2118 rows in total. n simply counts 0 to 7, i counts from 0 to 244. — RichardW
– RichardW, Commented May 30, 2014 at 19:30

CodeJockey · Accepted Answer · 2014-05-30 19:33:44Z

1

Error 1004 usually occurs when you try to access a cell that is out of bounds e.g. Cells(0, 1). Consider the case where page = 0, i = 0 and n = 0:

You are accessing Cells(0 * 4 + 0 * 7 + 0, m) --> Cells(0, m)

This causes Excel to throw error 1004.

answered May 30, 2014 at 19:33

CodeJockey

1,9811 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Orphid Over a year ago

This looks correct. When i = 0 (i Mod 5) - 1 does not equal 0 (it equals -1), so the page counter is never incremented in the first iteration of the outside loop. By the time you hit the second, nested loop, page is still 0, and sheet indexes start at 1, 1 - not 0, 0. The OP should consider debugging line by line using F8, which will allow him to see the value of page by hovering his mouse over it.

RichardW Over a year ago

This is technically the issue, but not the solution. The actual problem is that my "If ((i Mod 5) - 1) = 0 Then" line should be "If (((i+1) Mod 5) - 1) = 0 Then". Now, it places the first header and counts the first page before it gets to the loops to copy the elements over. Tadah!

CodeJockey Over a year ago

You came seeking an answer to the question "why isn't this line of code executing" not for a completed solution. What satisfaction would you gain if we fixed the whole thing for you? We would never deprive you of the joy derived from solving this puzzle yourself!

Collectives™ on Stack Overflow

Can't use a particular variable within a nested for loop?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related