vba Formula Problem

Lochnagar

New Member
Joined
Jan 28, 2008
Messages
43
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,543
Office Version
365, 2016, 2010
Platform
Windows
"=" & ActiveCell.Offset(0, -1).Address(False, False) & " + " & ActiveCell.Offset(0, 1).Address(False, False)
 

Colin Legg

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
365, 2016
Platform
Windows
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:
Code:
    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

Legend
Joined
Jun 19, 2002
Messages
63,651
Perhaps

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

Lochnagar

New Member
Joined
Jan 28, 2008
Messages
43
Brillliant, they work spot on! Thanks everyone for your help, it's very much appreciated. :)

Lochnagar
 

Watch MrExcel Video

Forum statistics

Threads
1,102,274
Messages
5,485,783
Members
407,515
Latest member
franjey

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top