Formula should shift one column in the formula

0
votes

The formula in Excel is this,

=FUT_CHAIN!$D$9-LME!$F$2

Each time I click the VBA, it should shift one column in the formula but only limited to the first component, which means that the formula would be,

=FUT_CHAIN!$D$10-LME!$F$2

The $ inside the formula and the second component can not be changed or eliminated.

1
excelvba

1 Answers

0
votes

Your narrative states 'each time I click the VBA, it shifts one column in the formula' but the example result formula is one row shift.

For one column shift try,

sub shiftRight()
    dim f as string
    f = mid(activecell.formula, 2)
    activecell.formula = chr(61) & range(split(f, chr(45))(0)).offset(0, 1).address(external:=true) & chr(45) & split(f, chr(45))(1)
end sub