Find then Replace Adjacent Cell with New Number

Hi n0mad,

I need a simple way to search the entire workbook for Barry and replace left adjacent cell

That is a relative statement based on what your workbook actually looks like.

I'm not even sure a formula is going to work for you as in can't replace the contents of an already filled in cell.

This is not nearly enough to work with.

The above is in separate columns ie 10 = A1 and Barry = B1
Note: Not always in Column A & B

Please post a representative copy of what your workbook looks like with as many scenarios as possible. Before and after is always welcomed.

So you just want the 10 to the left of Barry replaced with a 5 and that's it?

What about your comment?

Note: Not always in Column A & B

And are you wanting to use VBA?

For a quick easy fix, why not just filter on the column with Barry, select the cells starting from the first cell, type in 5 and select Ctrl + Enter.

All your change to Barry will be applied to once.

Try this on a copy of your workbook. Code will work on all the worksheet in the workbook.

Please Login or Register  to view this content.

Paste code in a Normal module

• Where to paste code
• Highlight macro on Forum >> Ctrl + C >> Open your workbook
• Alt + F11 >> opens the Visual Basic Editor (VBE)
• Ctrl + R >>opens the Project Explorer (if not already open on left side of screen)
• Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
• Paste code >> Ctrl + V (right side of screen)
• Alt + Q >> exits VBE and returns to Excel
• Back in Excel >> Alt + F8 >> Macro Dialog Box >> Highlight macro >> Run

I tested your spreadsheet attached and when executing the MACRO it only works on EG Sheet 1 only on A1 and B1

What About A4 and B4 that also has a Barry? I have Barry all over my Spreadsheets

That is why I asked for you to provide a spreadsheet as to exactly what you have. Once you provide a representative spreadsheet I will take another look.