CODEWITHSUNDEEP
vbaexcelexcel-formula
Nate
Nate

Reputation: 71

Excel macro to add value to a cell with a formula

I am trying to set up a macro to add a value, say 10, to an existing cell that has a formula in it. I would like the cell to maintain the formula.

Here is what I've done so far:

Sub addvalue 
'Keyboard Shortcut: Ctrl+Shift+A

ActiveCell.Formula = ActiveCell.Formula + 10

End Sub

This isn't working for me. It works when the cell is just a number, but not when the cell I am trying to adjust is a formula.

If the cell I'm trying to adjust contains =A1 + 4 and I run this macro, I'd like it to be =A1 + 14 after the macro runs.

Upvotes: 7

Views: 3608

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

getting to change the formula itself is problematic. but you can append the +10 to the end and get =A1 + 4 + 10 with:

ActiveCell.Formula = ActiveCell.Formula & "+ 10"

The issue is that ActiveCell.Formula returns a string and you cannot add a number to a string. You need to concatenate the string and the new part.

Edit

To make it so it changes the value instead of concatenating:

Sub addvalue()
'Keyboard Shortcut: Ctrl+Shift+A
Dim strsplit() As String
Dim i As Long
Dim dn As Boolean


dn = False

strsplit = Split(Mid(ActiveCell.Formula, 2), "+")
For i = LBound(strsplit) To UBound(strsplit)

    If IsNumeric(strsplit(i)) Then
        strsplit(i) = CDbl(strsplit(i)) + 10
        dn = True
        Exit For
    End If
Next i

ActiveCell.Formula = "=" & Join(strsplit, "+") & IIf(dn, "", "+ 10")

End Sub

This may not work in all cases but for simple formulas it does.

Upvotes: 6

Related Questions