- You are here
- Microsoft Newsgroups Archived.At
- microsoft.public.excel.misc
- 2007 August
- Customized Find/Replace to Update Numbers Using Specified Criteria

Subject: | Customized Find/Replace to Update Numbers Using Specified Criteria |

Posted by: | Cube Slave |

Date: | Fri, 17 Aug 2007 |

I have a frequently-updated report with many cells that contain both text and

numbers. Often, the numbers--and only those falling within a certain

numerical range--need to increment by a set amount, say four or five. Is

there a way to update numbers which fall within a specified numerical range

by a specified amount? I know this isn't clear, so here's an example: Say

that, in Column A, there are 5000 filled cells, each of which contains

various text descriptors with numbers scattered amongst them:

Cell A1: Pig, 100, dog, 512, cat, 999, orangutan, 550

Cell A2: Pig, 102, raging bull, 551, cow, 601

Cell A3: Rabid dog, 201

What I need to do is tell Excel to look through all of the cells and,

whenever it finds numbers between 500 and 600, to increase them by 4, but to

leave everything else alone. The desired result would be:

Cell A1: Pig, 100, dog, 516, cat, 999, orangutan, 554

Cell A2: Pig, 102, raging bull, 555, cow, 601

Cell A3: Rabid dog, 201

I know I can just do a series of find/replace actions, but this report

updates often and the range of numbers that may need to update varies

frequently as well (not to mention the increment change amount, so it's a

whole lot of work. Is there a macro or add-in that will do a find-replace

using criteria that the user will supply, perhaps via a user form? I know a

little VBA, but this is pretty complicated and I have no idea. If text and

numbers were iin separate cells, I'm sure this would be easier, but

unfortunately I have to work with the yucky report that I'm given.

I would really, really appreciate any help on this. I'm in way over my head.

- RE: Customized Find/Replace to Update Numbers Using Specified Criteria posted by Farhad on Fri, 17 Aug 2007