Variable resetting to 0

Question

I am just starting out with Excel VBA, and am trying to run the below script to populate a table with values from several worksheets.

I am having trouble with loops with the dindx variable; at step 13, it resets to 0, which means that end up overwriting data in the same row in within worksheet "Dest 2 ".

Hoping someone can help point out what I'm doing wrong here as I'm a bit stumped :)


Sub fixDataCBAttemptOnly()

    '1. Establish where output goes
    Set dest = Sheets("dest 2").Range("B6")

    '2. Set counter for output rows
    dindx = 0

    '3. Cycle through all sheets
    For Each ws In Sheets

        '4. Test if current sheet is not "dest"
        If ws.Name <> "Dest 2" Then

            '5. True Case:  Grab left 4 chars of name of sheet and call year
            Yr = Left(ws.Name, 4)

            '6. Grab from char 6 through end to set as region
            Rgn = Right(ws.Name, Len(ws.Name) - 5)

            '7. Set source anchor for active sheet
            Set srce = ws.Range("C7")

            '8. Loop from 0 to 11 for each column of months
            For cindx = 0 To 11

                '9.  Month value set to counter +1 (i.e Jan is when cindx is 0, etc)
                Mnth = cindx + 1

                '10. Initialize row counter to zero
                rindx = 0

                '11. Loop as long as anchor cell down isn't blank
                While srce.Offset(rindx, -1) <> ""

                    '12. Test if not empty; if not empty populate destination with data
                    If srce.Offset(rindx, cindx) <> "" Then

                        '13. True Case:  Output as:  Cust ID; Date; Value; Region; Year
                        dest.Offset(dindx, 0) = srce.Offset(rindx, -1)
                        dest.Offset(dindx, 1) = Yr & "-" & Format(Mnth, "00")
                        dest.Offset(dindx, 2) = srce.Offset(rindx, cindx)
                        dest.Offset(dindx, 3) = Rgn
                        dest.Offset(dindx, 4) = Yr

                        '14. Increment dest counter
                        dindx = dindx + 1

                        '15. End if statement from #12
                    End If

                    '16. Increment row indx
                    rindx = rindx + 1

                    '17. End while statement from #11
                Wend

                '18. Go to next month from #8
            Next cindx

            '19. End if from #4
        End If

        '20. Go to next sheet from For loop in #3
    Next ws

End Sub

Turn on (and **always** use) `Option Explicit` ([explanation](https://www.excel-easy.com/vba/examples/option-explicit.html)). This will make sure your `dindx` variables are the same and don't have a "special" character embedded. — PeterT, May 08 '20 at 12:48
`dindx` is not declared to be of any type. Make sure you declare **all** your variables before first use. Eg `Dim dindx As Long` — Pᴇʜ, May 08 '20 at 12:57
An alternative to `Offset` is just to [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and add 1... within the loop, so you ensure that you're always writing to the next available row. — BigBen, May 08 '20 at 13:00
Do you have your row/column loop nesting reversed? Looks like you need to loop rows then columns, not columns then rows — Tim Williams, May 08 '20 at 15:41

score 2 · Accepted Answer · edited May 10 '20 at 15:15

2

PeterT's and Peh's comments solved the issue - I hadn't declared my variables, as soon as I did this for dindx the code worked correctly.

edited May 10 '20 at 15:15

John Alexiou

28,472
11
77
133

answered May 10 '20 at 13:23

cbro4_4

21
4

Variable resetting to 0

1 Answers1