jeudi 22 septembre 2016

If statement results overwriting each other VBA

I am experiencing a problem with the outputs from my loop. As the sub is running I can see that the results from the final IF statement are being overwritten by the results from the second one. My code is structured as follows:

for i = 1 to 5
    for j = 1 to 50
        for each events.value in eventArray
            if events.value = arrayElem then
                if cells(i,j).value = "x" then
                    type = "col1"
                elseif cells(i,j).value = "y" then
                    date = "col2"
                elseif cells(i,j).value = "z" then
                    num = "col3"
                end if

                count = count + 1

                activeworkbook.worksheets("output").cells(count + 1, 1) = type
                activeworkbook.worksheets("output").cells(count + 1, 2) = date
                activeworkbook.worksheets("output").cells(count + 1, 3) = num

            end if
        next arrayElem
        if cells(i,j).value = "a" then
            name = "row1"
        elseif cells(i,j).value = "b" then
            size = "row2"
        elseif cells(i,j).value = "c" then
            height = "row3"
        end if

        activeworkbook.worksheets("output").cells(count + 2, 1) = name
        activeworkbook.worksheets("output").cells(count + 2, 2) = size
        activeworkbook.worksheets("output").cells(count + 2, 3) = height
    next j
next i

Obviously these are dumby variables and results, but the overall structure is the same as the real code. I can see "name","size", and "height" being printed, but then they get replaced by "type", "date", and "num". How do I prevent this from happening? Each time a new event is found I need it to print its associated characteristics printed into a new row in the "output" sheet.

Aucun commentaire:

Enregistrer un commentaire