The Excel Evangelist: December 2012

I just finished this yesterday and Im very pleased with it.

The purpose is to Find every instance of a word in an entire workbook, and replace a cell X spaces to the right.

The code looks like this:

Sub findreplaceoffset()
Dim Stext As String
Dim Oset As Integer
Dim Rtext As String
Dim CheckVal As String
Dim ws As Worksheet
Dim result As Range

Stext = Application.InputBox("Type in the word you want to find")
Oset = Application.InputBox("Type in the Column offset to be replaced")
Rtext = Application.InputBox("Type in what you want replaced")

For Each ws In Application.ThisWorkbook.Sheets
ws.Activate
Range("A1").Activate

Do While CheckVal <> Rtext

On Error GoTo Skip:
   Set result = Cells.Find(What:=Stext, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

        If result Is Nothing Then
        GoTo Skip:
        End If

        result.Activate

        CheckVal = result.Offset(0, Oset).Value

        result.Offset(0, Oset).Value = Rtext

Loop
CheckVal = ""

Skip:
Next ws
End Sub

In this sample the workbook contains quotations with items and amounts.

You can now download the sample sheet that contains the macro and follow along.

Find_Replace_Offset_.xlsm

Today lets suppose we need to change the price of all of the Apples in every sheet to $100

Run the macro and an input box request what word you want to find.

Type "Apples"