Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Bloomington, Indiana, USA
    Thanked 0 Times in 0 Posts

    Macro to move cell contents based on a condition (2003)

    I am trying to set-up a macro to look in my worksheet and for every instance of the string "OOR" in column G move the contents of cells in columns H and I of the same row to Cell J and K of the same row. I then need to replace the contents of H and I with 0. I looked in VBA help and in this forum and figure that I will need to use something like range.offset to do this, but I can't figure out how to have this look for the "OOR" in G to begin with, and then cycle through the remaining rows. Depending on the time of year, this spreadsheet may have over 1000 rows. I have attached a sanitaized version of the spreadhsheet. Thanks in advance for any assistance.
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Macro to move cell contents based on a condition (2003)

    Try this:

    Sub Macro1()
    Dim oCell As Range
    Dim strAddress As String
    With Range("G:G")
    Set oCell = .Find("OOR", LookIn:=xlValues)
    If Not oCell Is Nothing Then
    strAddress = oCell.Address
    With oCell.Offset(0, 1).Resize(1, 2)
    .Copy Destination:=oCell.Offset(0, 3)
    .Value = 0
    End With
    Set oCell = .FindNext(oCell)
    Loop While Not oCell Is Nothing And oCell.Address <> strAddress
    End If
    End With
    End Sub

Posting Permissions

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