Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    LOOP (EXCEL 2002)

    Hi all,

    I'm trying to create a loop that deletes up to 6 cells to the left.....what I get now is it deletes the ocell find.....and then deletes to the left.....I would like to leave the ocell with it's value.

    Thanks,
    Darryl.

    Private Sub clearunit()
    Dim i As Integer

    Dim ocell As Range
    Set ocell = Cells.Find(XXX, , , , , xlNext, True, False)
    Do Until i < -6

    ocell.Offset(0, -i).Value = ""
    i = i + 1
    Loop
    Set iRange = Worksheets(1).Range(Cells(iRo, iroc), Cells(iRd, irdc))
    With iRange
    .Interior.ColorIndex = xlNone
    End With
    If XXX = "Unit 1" Then
    ActiveSheet.Shapes("Unit 1c").Select
    Selection.Characters.Text = ""
    ActiveSheet.Shapes("Unit1dr").Select
    Selection.Characters.Text = ""
    ActiveSheet.Shapes("Unit1op").Select
    Selection.Characters.Text = ""
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LOOP (EXCEL 2002)

    You don't assign i an initial valie, so it starts out with the default value of 0. The first time through the loop, i = 0 so ocell.Offset(0, -i) is the cell itself.
    Within the loop, you increase i, so the condition i < -6 will never be met. Try this instead:

    For i = 1 to 6
    ocell.Offset(0, -i).Value = ""
    Next i

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: LOOP (EXCEL 2002)

    Thanks...

Posting Permissions

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