locked
Basic problem: undesired looping in alternate rows RRS feed

  • Question

  • 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!

    Thursday, September 13, 2012 2:07 AM

All replies

  • I have comment out what I believe are the incorrect lines and added amended line

    Code edited since initial post. Need to subtract 1 when from total loops when using For i = 0 To number - 1 otherwise it loops an extra time with the zero. 

    Private Sub loopingName()
         Dim name As String, number As Integer, k As Integer, i 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    'Will leave blank cell after each name change
                      For i = 0 To number - 1  'Follow on immediately without blank cell
                          'Cells(k + i - 1, 2).Value = name    'Don't subtract i
                          Cells(k + i, 2).Value = name   'Amended line
                          'i = i + 1  'Don't increment "i". "i" increments with each loop
                      Next i
                  End If
         Next k
     End Sub


    Regards, OssieMac


    • Edited by OssieMac Thursday, September 13, 2012 3:05 AM
    Thursday, September 13, 2012 2:59 AM