[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

Excel VBA to Find Data and Insert specitic data in cell to the right

I have a piece of Excel VBA code that does a global search and replace for a variety of values.  

Once this is done, I want to also have the macro insert a specific value in the column to the left of the found values... For instance, I have 100 rows with a value of "468x60", I want the macro to insert to the column on the left the value "CPM"...  Then when it finds the next 100 rows of "Messenger" I want the macro to replace the value to the left with "Messenger Ad"....

Any help would be appreciated!!!
Sub FixDimension()
    Cells.Replace What:="Full Banner - 468 x 60", Replacement:="468x60", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="Skyscraper - 120 x 600", Replacement:="120x600", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="Medium Rectangle - 300 x 250", Replacement:="300x250", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="Super Banner - 728 x 90", Replacement:="728x90", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="GEOPOP - 1 x 1", Replacement:="GeoPop", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="780x260 - 780 x 260", Replacement:="Bottom of Page", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="Slider - 1 x 1", Replacement:="Messenger Ad", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="Raw Click - 1 x 1", Replacement:="Raw Click", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="Interstitial - 1 x 1", Replacement:="Interstitial", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="Pixel/Popup - 1 x 1", Replacement:="Pop Under", LookAt:=xlWhole, MatchCase:=False
End Sub

Open in new window

1 Solution
This perhaps? You don't say what you want inserted to the left, but the values would go in sOffset.
Sub FixDimension()

Dim rFind As Range, sFind(), sReplace(), sOffset(), i As Long

sFind = Array("Full Banner - 468 x 60", "Skyscraper - 120 x 600", "Medium Rectangle - 300 x 250", _
              "Super Banner - 728 x 90", "GEOPOP - 1 x 1", "780x260 - 780 x 260", _
              "Slider - 1 x 1", "Raw Click - 1 x 1", "Interstitial - 1 x 1", "Pixel/Popup - 1 x 1")
sReplace = Array("468x60", "120x600", "300x250", "728x90", "GeoPop", "Bottom of Page", _
                 "Messenger Ad", "Raw Click", "Interstitial", "Pop Under")

sOffset = Array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j")
With ActiveSheet.UsedRange
    For i = LBound(sFind) To UBound(sFind)
        Set rFind = .Find(What:=sFind(i), LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
                rFind.Value = sReplace(i)
                rFind.Offset(, -1) = sOffset(i)
                Set rFind = .FindNext(rFind)
            Loop While Not rFind Is Nothing
        End If
    Next i
End With

End Sub

Open in new window


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now