Using Search and Replace with VBA/Excel

Question

Got an annoying Excel/VBA issue that I can't seem to get around - would appreciate any help.

I have a formula in a spreadsheet that says something along the lines of if the cell to its left =1, then the cell itself =on, and if not, ="off". Is it possible to write some VBA search and replace code that turns "off" to "totally_off" when run?

At the moment it just seems to be editing the formula itself, while I really want it to delete everything in that cell and just replace it with the phrase "totally_off"

Thanks for your help.

If I'm understanding correctly, you could do a search and replace looking for *off* and replacing with totally off. — chuff
– chuff, Commented May 28, 2013 at 16:53
actually, the problem is that this will replace any cell with off in the formula, I only want to alter the ones where these cells have met the criteria to be outputting "off" — user2429452
– user2429452, Commented May 28, 2013 at 17:02
You could still do as @chuff suggested. Just click the "Match Entire Cell Contents Option" before you do the Replace. — Doug Glancy
– Doug Glancy, Commented May 28, 2013 at 17:17

chuff · Accepted Answer · 2013-05-28 18:38:54Z

1

The following code should work. Just replace the range B1:B10 with the range that the formulas you want to change are in. The procedure tests the value of the cell immediately to the left of the cells in the formula. If the value is zero, it replaces the formula with the "totally off".

Sub totally_off()
    Dim rng As Range
    Dim cell As Variant
    Set rng = Range("B1:B10")
    For Each cell In rng
        If cell.Offset(0, -1).Value = 0 Then
            cell.Value = "totally off"
        End If
    Next cell
End Sub

Here's a non-VBA, non-search and replace alternative.

Set up a filter on the formula column with Sort and Filter / Filter on the Home ribbon.
Select "off" from the drop-down menu on the column.
Type "totally off" in the first cell of the filtered column and copy it down to the bottom of the column.
Then remove the filter. The formulas evaluating to "on" will remain intact.

edited May 28, 2013 at 18:38

answered May 28, 2013 at 17:12

chuff

5,9061 gold badge24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter Albert · Accepted Answer · 2013-05-28 20:07:02Z

0

No need for VBA - simply use an AutoFilter:

Select the column with the on/off formula
Apply an AutoFilter (Ctrl-Shift-L
Filter for off
Again, select all values
Simply type in your replacement value totally_offinto the formula bar - but press Ctrl-Enter

Done!

answered May 28, 2013 at 20:07

Peter Albert

17.6k6 gold badges68 silver badges89 bronze badges

Collectives™ on Stack Overflow

Using Search and Replace with VBA/Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related