vba Formula Problem

[Lochnagar]

Hi,

I'm wanting to input a formula (and not a value) into the activecell, which adds the first cell to the left and right of the activecell e.g. if my activecell was B1 the formula would be "=A1+C1"

What I'm wondering is, is it possible to generalise the above, that is, if my activecell was E1 the formula that vba would insert would be "=D1+F1" or if it G1 the formula inputed would be "=F1+H1" and so on... ?

...something kinda along these lines

activecell = "=[cell, left of activecell] + [cell, right of activecell]"

Any help or suggestions would be greatly appreciated.

Thanks,
Lochnagar

 

[iliace]

"=" & ActiveCell.Offset(0, -1).Address(False, False) & " + " & ActiveCell.Offset(0, 1).Address(False, False)

 

[Colin Legg]

Hi,

I'm wanting to input a formula (and not a value) into the activecell, which adds the first cell to the left and right of the activecell e.g. if my activecell was B1 the formula would be "=A1+C1"

What I'm wondering is, is it possible to generalise the above, that is, if my activecell was E1 the formula that vba would insert would be "=D1+F1" or if it G1 the formula inputed would be "=F1+H1" and so on... ?

...something kinda along these lines

activecell = "=[cell, left of activecell] + [cell, right of activecell]"

Any help or suggestions would be greatly appreciated.

Thanks,
Lochnagar

Something like this, perhaps:

    With ActiveCell
        .Formula = "=" & .Offset(0, -1).Address(0, 0) & _
            "+" & .Offset(0, 1).Address(0, 0)
    End With

Bear in mind the implications if the activecell is in the first or last column of the worksheet....

 

[VoG]

Perhaps

ActiveCell.FormulaR1C1 = "=RC[-1]+RC[+1]"

 

[Lochnagar]

Brillliant, they work spot on! Thanks everyone for your help, it's very much appreciated.

Lochnagar