Customized Find/Replace to Update Numbers Using Specified Criteria

Giganews Newsgroups
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.

Replies