Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Skipping oCell (XP: SP3)

    How does one skip a "oCell" in the following code:

    For Each oCell in Range("A2:C500")
    If oCell = oCell.Offset(-1, 0) Then
    'Do Nothing
    Else
    Selection.EntireRow.Insert
    oBottom = oCell.Row
    oCell.Offset(-1, -2) = oCell.Offset(-2, -2)
    oCell.Offset(-1, -1) = 61055
    oCell.Offset(-1, 0) = oCell.Offset(-2, 0)
    oCell.Offset(-1, 2).Formula = "=Round(Sum(E" & oBottom - 2 - oCounter & ":E" & oBottom - 2 & "),2)"
    oCounter = 0
    End If
    Next oCell


    The code is basically inserting a line between the "oCell" that don't match. The issue is that I don't know how to advance one "oCell" and then loop to the next oCell.

    Thanks,
    John

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Skipping oCell (XP: SP3)

    You have other problems with this code, such as Offsetting to invalid references (for example left of column A and above row 1).

    To loop through a single column to insert rows, start at the bottom and step 'backwards' (up):

    Dim oRng As Range
    Dim oCounter As Long

    Set oRng = Range("A2:A500")
    For oCounter = oRng.Rows.Count To 1 Step -1
    With oRng.Cells(oCounter)
    If .Value <> .Offset(-1, 0).Value Then
    .EntireRow.Insert
    End If
    End With
    Next oCounter
    Set oRng = Nothing
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Skipping oCell (XP: SP3)

    John,

    <You have other problems with this code, such as Offsetting to invalid references (for example left of column A and above row 1).>

    In my haste to paste an example of the code I typed the Range("A2:C500") incorrectly it should have been Range("C2:C500").

    I'll give your suggestion a try - working backwards.

    Thanks,
    John

Posting Permissions

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