VBA to change cell reference of formula within a given cell

[SixSix]

Afternoon all,
Been lurking around the forums on and off for some time.

Its been a while since i needed to do anything in VBA and as such I am very rusty but hoping you guys can help me out with something.

I need to be able to loop through each cell of a selected range, and for each cell, i need to change any row values given in any/all formulas within the cell.
The new row value will be an increment off a number given by the end user.
so for example:

a value of 125 could be given,

=mySheet!$B$10+mySheet!$B$19+mySheet!$B$20

Would become

=mySheet!$B$135+mySheet!$B$144+mySheet!$B$145

I have only used 1 cell as an example here but the reason VBA is needed is because the cells could contain a different formula and I could need to change anything up to 100 or so cells.

I know its possible as I have done it in the past, but like an idiot i forgot to save it somewhere for future use.

I hope that makes sense and I haven't overly complicated things.

The code I have so far is as follows:

Please Login or Register  to view this content.

Thanks in advance for any help given.

[ragulduy]

something like:

Please Login or Register  to view this content.

Might work but it depends how much your formulas vary from the example.

[SixSix]

something like:

Please Login or Register  to view this content.

Might work but it depends how much your formulas vary from the example.

You must headed in the right direction, but instead of adding the number it just appends it, so:

=mySheet!$B$10+mySheet!$B$19+mySheet!$B$20

becomes

=mySheet!$B$10125+mySheet!$B$19125+mySheet!$B$20125

[ragulduy]

hi - it worked ok on the example I tried it with so not sure what is happening for you. Perhaps try:

Please Login or Register  to view this content.

[SixSix]

I now get type mismatch on this line:

Please Login or Register  to view this content.

[ragulduy]

Sorry, both solutions worked ok on my machine. I don't know what the problem is.

Are you using the code exactly as posted?

[SixSix]

I had to slightly change it as the value to add would change each time so this is how I reused it, perhaps I butchered it somehow?

Please Login or Register  to view this content.

I suspect It's got something to do with how 'val' is being set?

[ragulduy]

Still works for me...

[SixSix]

Hmmm how very odd.
Possibly version of excel but not sure how this would be an issue in this instance.
I am using 2007

[SixSix]

Ah ok so if I select few less cells it works, so it clearly doesn't like a particular formula.
Thanks for your help on this.
Will give it another bash in the morning.

[SixSix]

Ok I found what was causing the issue, could see in your code that you were using the $ symbol to work out the positions, so looking through the code I could see that some formulas were not absolute.
Once i change the formulas to absolute via another macro I have, this code worked.

Many thanks for your help.

Please Login or Register  to view this content.