Replacing part of formula in many cells with VBA

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

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

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

Please Login or Register  to view this content.