CODEWITHSUNDEEP
vbaexcelfor-loop
blazeaster
blazeaster

Reputation: 21

Simple VBA loop overwrites the cell values

I tried googling it and i tried to solve it on my own but no luck.

I have a sheet that needs to be filled with specific results from another sheet. I'm getting the accurate results in a debug window and msgbox, but when i apply it to the sheet it keeps overwriting till the last result meeting the criteria.

I also tried putting the results in array, but only the first row in array gets populated with all of the data.

Edit: I'm sorry for not explaining correctly. GlobalniIndentifikator is ID. I have two sheets. One has only one id per row and the other one has the same ID for multiple rows. I know the issue is that ID is a constant, but I have no idea how to solve without getting the wrong data.

  Dim x As Integer
  Dim y As Integer
  Ukupnoredova = Sheet2.Cells(Rows.Count, 8).End(xlUp).Row

  For x = 2 To Ukupnoredova

     If Sheet2.Cells(x, 8) = GlobalniIndentifikator Then

            Sheet7.Cells(8, 1) = Sheet2.Cells(x, 1)
            Sheet7.Cells(12, 1) = Sheet2.Cells(x, 1)
            Sheet7.Cells(16, 1) = Sheet2.Cells(x, 1)
            Sheet7.Cells(20, 1) = Sheet2.Cells(x, 1)
            Sheet7.Cells(24, 1) = Sheet2.Cells(x, 1)
            Sheet7.Cells(28, 1) = Sheet2.Cells(x, 1)
            Sheet7.Cells(32, 1) = Sheet2.Cells(x, 1)

    End If

  Next x

Upvotes: 0

Views: 458

Answers (1)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

What @Jeeped has suggested...

Dim x As Integer
Dim y As Integer
Dim lngCnt As Long
Ukupnoredova = Sheet2.Cells(Rows.Count, 8).End(xlUp).Row

lngCnt = 1
For x = 2 To Ukupnoredova

    If Sheet2.Cells(x, 8) = GlobalniIndentifikator Then

        Sheet7.Cells(8, lngCnt) = Sheet2.Cells(x, 1)
        Sheet7.Cells(12, lngCnt) = Sheet2.Cells(x, 1)
        Sheet7.Cells(16, lngCnt) = Sheet2.Cells(x, 1)
        Sheet7.Cells(20, lngCnt) = Sheet2.Cells(x, 1)
        Sheet7.Cells(24, lngCnt) = Sheet2.Cells(x, 1)
        Sheet7.Cells(28, lngCnt) = Sheet2.Cells(x, 1)
        Sheet7.Cells(32, lngCnt) = Sheet2.Cells(x, 1)
        lngCnt = lngCnt + 1

    End If

Next x

Upvotes: 1

Related Questions