Changing part of a formula in a range of cells

**Glyndo** · 04-20-2009, 03:55 PM

Hi all!

First post, so bear with me.....

I've got a spreadsheet that compares two postcodes and calculates the distance between them. The code I'm using is

Please Login or Register  to view this content.

This works fine, but the problem is I've got over 3,200 different rows of postcodes to check and also need to do it for 92 different postcodes. Meaning I have to check the distances of 92 different postcodes from each of the 3,200. If that makes any more sense!

Anyway, what I need to do is change the part of the code that says

Please Login or Register  to view this content.

in the above code, and change it to

Please Login or Register  to view this content.

Obviously for each row, the cell number changes.

Is there a quick way of me being able to change this part of the formula for all the rows in the spreadsheet, or have I got to do it all manually?

**mewingkitty** · 04-20-2009, 03:58 PM

You NEVER have to do a repetitious task manually.
If you could provide some more information, such as the columns&rows which contain these formulas, or possibly post an example workbook, we'll get ya up and running in no time

**Glyndo** · 04-20-2009, 04:03 PM

Thanks for the quick reply!

In Column A I've got the list of all the different postcode (3,222 of them). Then in Row 2, from Column C to Column CP I've got the 92 postcodes that I need to check them against.

How would I post an example workbook? You mean as an attachment?

**mewingkitty** · 04-20-2009, 04:19 PM

Yes, kinda like this, see the attached file.
Check out the formulas in column a before you press that there button. They're various nonsense formulas, some of which contain (A31,4). The code which the button runs will replace any instance of (A31,4) in column A from rows 1 to 10 with ($D$2,4), provided it's within a formula.

Code is as follows for anyone curious:

Please Login or Register  to view this content.

At the moment it checks 10 rows. Pretty easy to change that to check 32000.

Let's see if that's what you're after.

Then in Row 2, from Column C to Column CP I've got the 92 postcodes that I need to check them against.

One thing at a time, lets get part 1 working and then move on to whatever else you need

mew!

**Glyndo** · 04-20-2009, 04:51 PM

Originally Posted by mewingkitty

Yes, kinda like this, see the attached file.
Check out the formulas in column a before you press that there button. They're various nonsense formulas, some of which contain (A31,4). The code which the button runs will replace any instance of (A31,4) in column A from rows 1 to 10 with ($D$2,4), provided it's within a formula.

The original formula doesn't have (A31,4) in every cell; as the number of the row increases, so does the number in the bracket. The original formula in the code in my first post was the formula from cell C31, that's why A31 was in the code. So cell C32 has (A32,4), C33 has (A33,4) and so on. Sorry, I should have made that clear in the first post.

Regarding the code, how do I transfer that into my spreadsheet and run it? I'd need it to replace the (A*,4) in Column C.

Sorry for the confusion

**mewingkitty** · 04-20-2009, 05:06 PM

Please Login or Register  to view this content.

You can run that as a macro, by inserting that into the macro code screen.

I still don't have an example, so I'm kinda working blind still.
This starts on row one, of column C. and goes to line 32000. Replacing any reference of (A ~current row~ ,4) with ($D$2,4)

If this does not solve your issue... PLEASE post an example workbook in order to get you a solution more quickly.