Changing Cell References Through VBA

[Rustyroof]

I was given a workbook with data that refers to a master data set. In B2 there is a place for user to enter the number of batches. B3 to B50 contain formulas that multiply B2 by a reference in a master data set. When it was written the formulas were written so they could be copied down. (ie =$B$2*'Sheet1'!A2) then this formula was copied down B50. Now we want to have multiple batches on one sheet and would like to change all the formulas from =$B$2*'Sheet1'!A2 to B2*'Sheet1'!$A$2, =$B$3*'Sheet1'!A3 to B2*'Sheet1'!$A$3, and so on. I could go through the cells and change with F4, but I need to do this in 50 workbooks. Can this be changed with VBA.

Thanks
Chris

[Arkadi]

If I read it right all you are trying to change is adding $ to the A2, A3, etc? and removing the $ from the B references?

[Rustyroof]

Yes....Long story short need to change both cell references in formula already in cell. Remove $ in one part and add $ to the other.

Chris

[Arkadi]

Try this one out:

Please Login or Register  to view this content.

[Rustyroof]

I would like to say a big thank-you for this. This is exactly what I was looking for. I did, however, have to make 1 modification to make this work. I needed to change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

but this works perfectly.

Thanks
Chris