Replacing part of formula in many cells with VBA

[lukestkd]

Hello all,
I need to replace the following part of a formula:

Please Login or Register  to view this content.

With this:

Please Login or Register  to view this content.

This is part of a larger formula:

Please Login or Register  to view this content.

where B5 changes based on the row/column.

• Actually I only really need to change the vlookup from column 3 to column 5.
• Alongside this I also need to change the text "KS2Av" to "KS3Av"
• I need this to happen in range A:AJ, rows 8,16,27,35 all in sheet "Seating Plan" (ranges: A8:AJ8, A16:AJ16, A27:AJ27, A35:AJ35)

I then need vba to do the reverse, so that I can set the button up to show KS2Av on one click, show KS3Av on other click. I can't upload the file as it contains confidential data.

Your help is greatly appreciated! Luke

[:) Sixthsense :)]

What about using the Find & replace Dialogue box?

[lukestkd]

I need it to be automated because it will be used by non-excel'ing staff

[tehneXus]

This might help: http://msdn.microsoft.com/en-us/libr.../ff194086.aspx
let me know if this does not help you.
Regards

[JOHN H. DAVIS]

Not sure I understand, but this may help to get you started?

Please Login or Register  to view this content.

[tehneXus]

Hi,

this is replacing as you required in Post#1:

Please Login or Register  to view this content.

in column A

regards