PDA

View Full Version : Basic problem needs help: undesired looping in alternate rows



rogerlai
09-12-2012, 07:52 PM
Hi all,

I am a rookie in vba and wanna for loop sth on every line but the following code only loops in alternate lines. wt is wrong?

Private Sub loopingName()
Dim name As String, number As Integer, k As Integer

For k = 1 To 17000
If IsEmpty(Cells(k, 2).Value) Then
name = inputbox("please enter name", "looping")
number = inputbox("please enter number", "looping step 2")
For i = 1 To number
Cells(k + i - 1, 2).Value = name
i = i + 1
Next
End If
Next
End Sub

Thanks!

Teeroy
09-13-2012, 01:16 AM
Hi rogerlai,

Welcome to the forum. Please use the VBA tags to enclose your code as it makes it far more readable (and the moderators might get a bit narky otherwise).

Remove the line i=i+1 from your code and it will work. In a FOR loop the NEXT does the incrementing for you so in your example you add one, then NEXT adds one and you get alternate lines.

Bob Phillips
09-13-2012, 01:56 AM
Also, whilst it is hard to know what the inner loop is for, maybe it should be

Private Sub loopingName()
Dim name As String, number As Integer, k As Integer

For k = 1 To 17000
If IsEmpty(Cells(k, 2).Value) Then
name = InputBox("please enter name", "looping")
number = InputBox("please enter number", "looping step 2")
For i = 1 To number
Cells(k + i - 1, 2).Value = name
Next
k = k + i - 1
End If
Next
End Sub

to avoid re-processing those lines

Bob Phillips
09-13-2012, 01:58 AM
You can even remove the inner loop


Private Sub loopingName()
Dim name As String, number As Integer, k As Integer

For k = 1 To 17000
If IsEmpty(Cells(k, 2).Value) Then
name = InputBox("please enter name", "looping")
number = InputBox("please enter number", "looping step 2")
Cells(k, 2).Resize(number).Value = name
k = k + number - 1
End If
Next
End Sub