Shifting current formula reference in cell one column to the left

[Dosnox]

Hi All,

I've created some complicated macros that automate a lot of tedious work based on some spreadsheet. Now I'm on stuck on something I believe is quite simple?

I have tried to google this and work on it myself but couldn't come up with a solution.

Basically I need to create a macro that will shift the current cell formula one column to the left based on the formula in the cell

As simple example:

The current formula in a cell is ='Sheet1'!C2
The macro then changes this to ='Sheet1'!B2

If the current formula in a cell is ='Sheet1'!G2
The macro then changes this to ='Sheet1'!F2

Due to the nature of the spreadsheets I'm working with this needs to be a formula and not a value.

Thanks for your help in advance!
Shan

 

[Peter_SSs]

What would happen to any formula that refers to something in column A of Sheet1?

What is the name of the sheet these formula are on?