Changing formulae in VBA code????

[VBA Virgin]

Hello,

I'm quite new to this VBA language and I am struggling with the updating of formulae in code. Can anyone help????

I have a formulae in a workbook that refers to another sheet in the same workbook. The formulae looks like this:

='sheet 1'!BH32/'sheet 1'!BH9*5

So in the VBA code I need to update the formulae each week to refer to the next cell along i.e. if the reference is to cell BH this week I need it to be changed to cell BI next week in the workbook using code.

How can I do this???

Any suggestions greatly received!!

 

[Andrew Poulsom]

Welcome to MrExcel.

Can't you just use Edit|Replace, looking in Formulas?

 

[VBA Virgin]

Hi Andrew,

Thanks for your reply - I'm really sorry but as I'm not well versed in this, how do you mean ' use edit/replace looking in formulas'?

 

[Andrew Poulsom]

Select your formulas, choose Edit|Replace from the menu, enter BH in the What box, enter BI in the With box, check Formulas in Look in and click Replace All. If you turn on the macro recorder before doing it you will get some VBA code.

 

[VBA Virgin]

Brilliant !!
Thanks Andrew - will remember that one for future!