Excel

Find and Replace Through All Worksheets

Ease of Use

Easy

Version tested with

2000 

Submitted by:

Jacob Hilderbrand

Description:

Find and replace values through all the worksheets in a workbook, instead of one at a time. 

Discussion:

The built-in Find and Replace only works one worksheet at a time. Use this macro to find and replace through all the worksheets in your workbook. 

Code:

instructions for use

			

Option Explicit Sub ChgInfo() Dim WS As Worksheet Dim Search As String Dim Replacement As String Dim Prompt As String Dim Title As String Dim MatchCase As Boolean Prompt = "What is the original value you want to replace?" Title = "Search Value Input" Search = InputBox(Prompt, Title) Prompt = "What is the replacement value?" Title = "Search Value Input" Replacement = InputBox(Prompt, Title) For Each WS In Worksheets WS.Cells.Replace What:=Search, Replacement:=Replacement, _ LookAt:=xlPart, MatchCase:=False Next End Sub

How to use:

  1. Open Excel.
  2. Alt + F11 to open the VBE.
  3. Insert | Module.
  4. Paste the code there.
  5. Close the VBE (Alt + Q or press the X in the top-right corner).
 

Test the code:

  1. Tools | Macro | Macros...
  2. Select ChgInfo and press Run.
 

Sample File:

SearchAndReplace.ZIP 7.6KB 

Approved by mdmackillop


This entry has been viewed 434 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express