VBA For each loop not looping how i want

I have the following loop

r = 4
c = 4

Set userBeanList = XMLDOC.SelectNodes("/response/responseBody/responseList/item[recordType='TPI']/*[not(self::catch or self::reprive or self::cate or self::reet or self::aarg or self::crane)]")
For a_counter = 1 To 7
For Each userbean In userBeanList

Sheets("Sheet2").Cells(r, c) = userbean.nodeName

Sheets("Sheet2").Cells(r + 1, c) = userbean.Text
 r = r + 2
a_counter = a_counter + 1
If (a_counter = 7) Then Exit For

Next userbean
c = c + 1
r = 4
a_counter = 1
Next a_counter

which basically goes over my nodes prints the node name in row 4 followed by the node value in the cell below row 5, this repeats 6 times so that i get the node name followed by the node value in a list in a spreadsheet. I then have a counter which when it reaches 7 exits the for loop increments the column by 1 resets the row back to 4 and this repeats. However the For Each userbean in userBeanList appears to reset itself back to the start so rather than get the next userbean it gets the first one again, How can i amend my code below to get the next userbean in my nodes?

Solution

Try this:

r = 4
c = 4
a_counter = 1

Set userBeanList = XMLDOC.SelectNodes("/response/responseBody/responseList/item[recordType='TPI']/*[not(self::catch or self::reprive or self::cate or self::reet or self::aarg or self::crane)]")

For Each userbean In userBeanList
    Sheets("Sheet2").Cells(r, c) = userbean.nodeName
    Sheets("Sheet2").Cells(r + 1, c) = userbean.Text
    r = r + 2
    a_counter = a_counter + 1
    If a_counter  >= 7 Then
        c = c + 1
        r = 4
        a_counter = 1
    End If

Next userbean

You are trying to use a_counter as a for loop and as a separate counter you do not need the for loop it gets in the way.

Edit:

You really do not need the a-Counter at all. you can use the r value to check when 6 have been done:

r = 4
c = 4

Set userBeanList = XMLDOC.SelectNodes("/response/responseBody/responseList/item[recordType='TPI']/*[not(self::catch or self::reprive or self::cate or self::reet or self::aarg or self::crane)]")

For Each userbean In userBeanList
    Sheets("Sheet2").Cells(r, c) = userbean.nodeName
    Sheets("Sheet2").Cells(r + 1, c) = userbean.Text
    r = r + 2
    If r  > 14 Then
        c = c + 1
        r = 4
    End If

Next userbean