Share via

VBA - Copy a formula from one cell into another cell but adding a constant to it first. Final Cell must be a formula + Constant, not a static value.

Anonymous
2014-06-20T17:25:24+00:00

Hi,

I am trying to write a code in VBA to copy a cell which contains a formula and adding a constant (E.g. 4) to it and putting it in another cell.

E.g. If cell E4 has =Left(A3,4)*3, I want to put in cell E5 "=Left(A3,4)*3+4", so that when I change Cell A3, E4 and E5 change too.

I want to keep the results of E4 and E5 separate for my purpose.

How do I write this is VBA? Right now I can only get the static value of it (i.e. cell E5 says 4.35, instead of the formula + constant), so that when I change cell A3, E5 doesn't change as well.

Please help. I would imagine this to be simple but I can't seem to think of a VBA code for it right now.

Thanks everyone.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2014-06-20T17:45:39+00:00

    Sorry, I simplified the situation but I have in VBA the constant I want added (instead of 4), so I would prefer if I could write VBA. Is that possible?

    Thank you

    Hi,

    Like this

    Range("E5").Formula = Range("E4").Formula & "+4"

    But if you really want to use a constant, like this

    Dim MyConstant As Long

    MyConstant = 4

    Range("E5").Formula = Range("E4").Formula & "+" & MyConstant

    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-06-20T17:36:54+00:00

    Perhaps one of these,

    public const iADD as long = 4

    Range("E5").Formula = Range("E4").Formula & "+" & iADD

      ... or,

    Range("E5").Formula = "=E4+" & iADD

    0 comments
  2. Anonymous
    2014-06-20T17:36:38+00:00

    To clarify,

    My A3 cell is constantly changing as I am pulling data from an external source.

    My E4 cell is a manipulation of A3

    I want my E5 cell to be a manipulation of E4

    All in VBA, since the numbers I want to manipulate E4 with are calculated in VBA.

    I hope that clarifies my situation.

    Thanks everyone.

    0 comments
  3. Anonymous
    2014-06-20T17:31:50+00:00

    Sorry, I simplified the situation but I have in VBA the constant I want added (instead of 4), so I would prefer if I could write VBA. Is that possible?

    Thank you

    0 comments
  4. Anonymous
    2014-06-20T17:29:24+00:00

    Hi, not sure why you need it could you explain, don't need VBA in E5 you need to enter

    =E4+4

    0 comments