Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Certain cells in my workbook contains value 'A'. I want to replace the value 'A' with 'B'.

They scatter in several sheets. What is the most efficient way to implement the replacement in VBA?

share|improve this question
Maybe for another occasion, make sure 'A' is only specified once and all other instances are copies! – pnuts Sep 26 '12 at 0:44
Why can't you just search and replace? – Rhys Gibson Sep 26 '12 at 1:38
I need to automate this action. I am working on an conversion project which includes no less than 50 books. Part of the requirement is that the workbooks must be able to run in two different excel versions, 2002 and 2010, in parallel. The 2002 workbooks are constantly updated. – Anthony Kong Sep 26 '12 at 2:56
Multiple workbooks or multiple sheets (your question says sheets but the comment above says workbooks). If it's just 1 workbook, then you can do this with a Replace All, selecting Within: Workbook in the Options section. If it's multiple workbooks, then you probably are stick with some more complex VBA to open each workbook one by one and use something like Luke's solution on each of them. This is nasty. Is there not a better solution than constantly repeating a replace? – Rhys Gibson Sep 26 '12 at 5:00
up vote 2 down vote accepted

This works for me

For Each wb In Workbooks
    For Each sh In wb.Worksheets
        sh.Cells.SpecialCells(xlCellTypeConstants).Replace What:="a", Replacement:="b", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next sh
Next wb

All I did was use the macro recorder to record a find and replace operation, then add the loops.

If you don't want to make the change in all open workbooks, remove the outer For Next pair, but i figured this would make it easier to run over multiple workbooks at once.

share|improve this answer
This will also replace in formula, eg =A1+1 will become =B1+1, probably not what's required. To avoid this replace sh.Cells.Replace with sh.Cells.SpecialCells(xlCellTypeConstants).Replace – chris neilsen Sep 26 '12 at 8:04

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.