+ Reply to Thread
Results 1 to 7 of 7

cycle for

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-26-2009
    Location
    Slovakia
    MS-Off Ver
    Excel 2019
    Posts
    244

    cycle for

    Hello, I just need solve one simple problem (which drive me mad )
    so I have this code
        If Range("A3").Value <> "Draw" Then Range("A3").EntireRow.Insert Shift:=xlDown
        If Range("A6").Value <> "Draw" Then Range("A6").EntireRow.Insert Shift:=xlDown
        If Range("A9").Value <> "Draw" Then Range("A9").EntireRow.Insert Shift:=xlDown
        If Range("A12").Value <> "Draw" Then Range("A12").EntireRow.Insert Shift:=xlDown
    and I need to do it with command FOR, I try to do it but :
            For lngRow2 = 0 To 500 Step 3
                  
                      If Range("A" & lngRow2).Value <> "Draw" Then
                        Range("A" & lngRow2).EntireRow.Insert Shift:=xlDown
                      End If
            Next
    and there is some error (where?)

    or
            For lngRow2 = 0 To 500
                
                      If Range("A" & lngRow2 + 3).Value <> "Draw" Then Range("A" & lngRow2).EntireRow.Insert Shift:=xlDown
            Next
    but lngrow2 doesnt have +3 each cycle, only +1 ...

    Anyone could help me, please?
    Last edited by miso.dca; 12-07-2009 at 09:31 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: cycle for

    When inserting or deleting start at the end and work your way back to the start. That way the number referencing does not get out of sync.

        Dim lngRow2 as long
    
        For lngRow2 = 501 To 3 Step -3
            If Cells(lngRow2,1).Value <> "Draw" Then
                Cells(lngRow2,1).EntireRow.Insert Shift:=xlDown
            End If
        Next
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Contributor
    Join Date
    07-26-2009
    Location
    Slovakia
    MS-Off Ver
    Excel 2019
    Posts
    244

    Re: cycle for

    thanks !
    but I cant count from the end, because sometimes cell containing "draw" in row e.g. 4, sometimes in 5 and If I use your solution It would work but I also got little bit mess so I have to go from 0 to 500 ...

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: cycle for

    Try this instead then,

    Sub x()
        Dim lngRow2 As Long
        
        lngRow2 = 1
        Do While lngRow2 <= 500
            If Cells(lngRow2, 1).Value <> "Draw" Then
                Cells(lngRow2, 1).EntireRow.Insert Shift:=xlDown
                lngRow2 = lngRow2 + 1
            End If
            lngRow2 = lngRow2 + 3
        Loop
        
    End Sub

  5. #5
    Forum Contributor
    Join Date
    07-26-2009
    Location
    Slovakia
    MS-Off Ver
    Excel 2019
    Posts
    244

    Re: cycle for

    sorry but it doesnt work at all I uploaded excel sheet with data and you macro ... If you had time to solve it I would be grateful
    Attached Files Attached Files

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: cycle for

    for that data example

    Sub x()
        Dim lngRow2 As Long
        
        lngRow2 = 3
        Do While lngRow2 <= 500
            If Cells(lngRow2, 1).Value <> "Draw" Then
                Cells(lngRow2, 1).EntireRow.Insert Shift:=xlDown
            End If
            lngRow2 = lngRow2 + 3
        Loop
        
    End Sub

  7. #7
    Forum Contributor
    Join Date
    07-26-2009
    Location
    Slovakia
    MS-Off Ver
    Excel 2019
    Posts
    244

    Re: cycle for

    great ! this works perfect thank you very much !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1