PDA

View Full Version : [SOLVED] For loop inside IF statement



tekwarp
12-11-2017, 11:27 AM
Hi guys,

Never used VBA before and basically just trying write this sub:


Sub Populate_Empties()
Dim i As Integer
Dim j As Integer
Dim n As Integer
Dim m As Integer
Dim k As Integer
' test for 50 rows...then change i from 2 to 278970
m = 2
For k = 3 To 8
For i = 2 To 50
If (IsEmpty(Cells(i, k).Value)) Then
m = i 'any statement
Else
j = i - 1
For n = m To j
Cells(n, k).Value = Cells(i, k).Value
m = i + 1
End If
End Sub


I keep getting error End If without Block

Any suggestions?

Dave
12-11-2017, 12:05 PM
Please use code tags. You need to end For loops with a Next. Use option explicit at top of code and select Debug in the code window. Also, note the sheet name specification. HTH. Dave

Option Explicit
Sub Populate_Empties()
Dim i As Integer, j As Integer, n As Integer, m As Integer, k As Integer
' test for 50 rows...then change i from 2 to 278970
m = 2
For k = 3 To 8
For i = 2 To 50
If (IsEmpty(Sheets("YourSheetName").Cells(i, k).Value)) Then
m = i 'any statement
Else
j = i - 1
For n = m To j
Sheets("YourSheetName").Cells(n, k).Value = Sheets("YourSheetName").Cells(i, k).Value
m = i + 1
Next n
End If
Next i
Next k
End Sub

Paul_Hossler
12-11-2017, 12:22 PM
1. I'd use Long instead of Integer since there are more rows that an Integer can address

2. Just using Cells without some worksheet reference defaults to the Activesheet

3. For performance, I'd use Application.ScreenUpdating = False


I assume that this is just a learning exercise, since it didn't seem to do anything on my test