Automating this action over an entire workbook?

Hi folks,

I have a workbook that contains 12 worksheets (months).

I want to automate the following operation:

If i find a certain word in a cell, i want to enter a particular number in the cell immediately to its right.

I'd also like to change the number in another cell of the same row to zero.

So as an example, if i find the word "private" in A1, i want to enter the number "600" in B1, and change whatever number is in G1 to zero.

This operation should only apply if the word ("private") is found in column A

If possible, I'd like to run this operation across all worksheets in one go.

Could anyone point me in the right direction of how to achieve this?

Thanks!

(here is a little dummy workbook with the problem mocked up)

replace2.xlsx

A cell can only have a value or a formula, not both. If you want to change something and add something elsewhere, you will need VBA.

You would have to loop through the sheets in the workbook and, within that loop, loop through the cells in column A testing them for the word "private"

Are you ok with VBA?

EDIT....

Many thanks, i got it working!

Sorry, one thing that i should have made more explicit in my OP:

I need this to work if the cell contains a certain word or number.

This solution only works if the cell contents = the search term.

I mean, if you use: If c.Value = "private", then, it should also work if the cell contains "private payment" ......... or "payment - Private".

So, also not case sensitive.

Sorry, i know i'm now asking a lot, but if i can get this working, it'll save me literally days of work!

Code example:

Please Login or Register  to view this content.

EDIT, next reply coming in a moment

Please Login or Register  to view this content.

Click RUN button and enter search word e.g, Private

See here ..... https://trumpexcel.com/visual-basic-editor/