+ Reply to Thread
Results 1 to 4 of 4

loop with inserting cells

Hybrid View

  1. #1
    Registered User
    Join Date
    10-15-2010
    Location
    Sacramento, CA
    MS-Off Ver
    Excel 2007
    Posts
    56

    Talking loop with inserting cells

    Hi, I'm trying to do a loop, which I have, but need some adjustments. The loop is to go down and whenever it finds 1324l it will insert a row in that column, pushing it to the right, about 3 times. What I have is
    Sub test()

    Range("b8").Select
    If ActiveCell.Offset(0, 2) = "1324I" Then
    Do
    Selection.insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(1, 0).Select
    Loop Until ActiveCell.Offset = 1

    End If
    what this is doing is inserting in every row a cell and pushing it to the right 1. What I would like it to do is insert a cell ONLY when it finds 1324l, and I want it to insert 3 rows. Any help would be great!
    thanks,
    Attached Files Attached Files
    Last edited by gill389; 01-18-2011 at 04:29 PM.

  2. #2
    Registered User
    Join Date
    10-15-2010
    Location
    Sacramento, CA
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: loop with inserting cells

    I updated the formula as im messing around with it, now its inserting cells on 1324l, but its not doing on all of them, on some of them it is, and some of them its not, sometimes its inserting cells on other random rows. Just need some guidence

    Sub test()

    Range("b8").Select
    If ActiveCell.Offset(0, 2) = "1324I" Then
    Do
    Selection.insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(1, 0).Select
    If ActiveCell.Offset(1, 0) <> "1324I" Then
    ActiveCell.Offset(1, 0).Select
    End If
    Loop Until ActiveCell.Offset = 1


    End If
    Last edited by gill389; 01-18-2011 at 09:16 AM.

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: loop with inserting cells

    Hi gill389

    Firstly, please use Code Tags to wrap your code (not Quote Tags).

    Try this code
    Option Explicit
    Sub test()
        Dim Rng As Range
        Dim iCell As Range
        Set Rng = Range("B8:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        For Each iCell In Rng
            If iCell.Offset(0, 2) = "1324I" Then
                iCell.insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            End If
        Next iCell
    End Sub
    Please note the code uses the "1" in Column B to find the last row.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: loop with inserting cells

    Hi gill389
    I reread your post and see you want to insert three "Columns" to the right. You said "Rows" but your code was inserting "Columns".

    This code inserts three "Columns" when it finds "1324I".
    Option Explicit
    Sub test()
        Dim Rng As Range
        Dim iCell As Range
        Set Rng = Range("B8:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        For Each iCell In Rng
            If iCell.Offset(0, 2) = "1324I" Then
                iCell.Resize(1, 3).insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            End If
        Next iCell
    End Sub

+ 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