Can Someone help with this For Loop?

[Saladsamurai]

I keep getting the error: "Next without For" where I have highlighted in Yellow.

I am trying to check the first cell of each row in the "Raw Data" sheet and if it is "Power" then write that entire row to the first available row in "Layout" sheet.

This might not be the best way, but I just want to know what is wrong with my For/Next couples?

[vba]Sub WriteEquipmentType()
Dim i, k, h As Integer
For i = 1 To 1000
If Worksheets("Raw Data").Cells(i, 1) = "Power" Then

For k = 1 To 1000
For h = 1 To 20
Worksheets("Layout").Cells(k, h) = _
Worksheets("Raw Data").Cells(i, h)
Next h
Next k

Next i
End Sub[/vba]

[p45cal]

End If missing:[vba]Sub WriteEquipmentType2()
Dim i, k, h As Integer
For i = 1 To 1000
If Worksheets("Raw Data").Cells(i, 1) = "Power" Then
For k = 1 To 1000
For h = 1 To 20
Worksheets("Layout").Cells(k, h) = Worksheets("Raw Data").Cells(i, h)
Next h
Next k
End If
Next i
End Sub [/vba](I can't highlight the End If in the code above without the www.thecodenet.com VBA tags going wrong)

[Saladsamurai]

Hi p45scal! Thanks! Now I have a new problem :/ I am in an endless loop now.

It just keeps rewriting the same row over again.

I have attached a sample file.

[VBA]Sub WriteLayouts()
Worksheets("Layouts").Cells.Clear
Dim i, k, h As Integer
For i = 1 To 1000
If Worksheets("Raw Data").Cells(i, 1) = "Power" Then

For k = 1 To 1000
For h = 1 To 20
Worksheets("Layouts").Cells(k, h) = _
Worksheets("Raw Data").Cells(i, h)
Next h
Next k
End If
Next i

End Sub[/VBA]

[Saladsamurai]

Update: If you go to the "Layouts" worksheet and then run the Macro by Alt+F8, you can actually see what it is really doing.

It is not an endless loop.

[p45cal]

It's not a never ending loop, just a very long one; you have 62 rows with 'Power' in the first column, and for each of those you have 1000 * 20 iterations which makes for about 1.25 million copy instructions.
Is this what you're looking for?[vba]Sub WriteLayouts()
Worksheets("Layouts").Cells.Clear
Dim i, k, h As Integer
k = 1
For i = 1 To 1000
If Worksheets("Raw Data").Cells(i, 1) = "Power" Then
For h = 1 To 20
Worksheets("Layouts").Cells(k, h) = _
Worksheets("Raw Data").Cells(i, h)
Next h
k = k + 1
End If
Next i
End Sub[/vba]

[Saladsamurai]

Yes. Except that I don't even want the word "Power" to be transfered over. Just the Values. So do I just need to add an "h+1" in the cells argument? Let's see now...

Thanks for your help p45scal!!

[p45cal]

[vba]For h = 1 To 20
Worksheets("Layouts").Cells(k, h) = _
Worksheets("Raw Data").Cells(i, h)
Next h [/vba]becomes either[vba]For h = 2 To 20
Worksheets("Layouts").Cells(k, h) = _
Worksheets("Raw Data").Cells(i, h)
Next h [/vba]or[vba]For h = 2 To 20
Worksheets("Layouts").Cells(k, h-1) = _
Worksheets("Raw Data").Cells(i, h)
Next h[/vba]depending on which result you prefer.