PDA

View Full Version : Can Someone help with this For Loop?



Saladsamurai
08-25-2009, 09:50 AM
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?

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

p45cal
08-25-2009, 10:04 AM
End If missing: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 (I can't highlight the End If in the code above without the www.thecodenet.com VBA tags going wrong)

Saladsamurai
08-25-2009, 10:16 AM
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.

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

Saladsamurai
08-25-2009, 10:24 AM
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
08-25-2009, 10:49 AM
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?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

Saladsamurai
08-25-2009, 11:25 AM
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
08-25-2009, 02:22 PM
For h = 1 To 20
Worksheets("Layouts").Cells(k, h) = _
Worksheets("Raw Data").Cells(i, h)
Next h becomes eitherFor h = 2 To 20
Worksheets("Layouts").Cells(k, h) = _
Worksheets("Raw Data").Cells(i, h)
Next h orFor h = 2 To 20
Worksheets("Layouts").Cells(k, h-1) = _
Worksheets("Raw Data").Cells(i, h)
Next hdepending on which result you prefer.