Adding a cell reference to existing formula

CamaDragon 70 Reputation points
2026-11-21T23:14:27.4133333+00:00

I have a macro that mostly works, but it is adding the value and not the cell reference.

Here's an example:

A1: 25.00

A2: 25.00

A3: 50.00

A5: 200.00

A7: =A1+A2+A3-A5

A10: 200.00 (ActiveCell)

Sub UpdateFormula()

Dim ws As Worksheet

Dim newcell As Range

Set newcell = ActiveCell

Set ws = ThisWorkbook.Sheets("Sheet1")

ws.Range("A7").Formula = ws.Range("A7").Formula & "+" **& newcell**

End Sub

When the above is run, it adds 200.00 to the formula (thus A7 = A1+A2+A3-A5+200).

I know it has something to do with the syntax, but I've tried different formats and I get errors. If I just use this: ws.Range("A7").Formula = ws.Range("A7").Formula & "+A10" it adds cell A10 as a reference.

What am I missing?

Developer technologies | Visual Basic for Applications

Answer accepted by question author

AI answer

2026-11-21T23:14:57.4966667+00:00

Append newcell.Address rather than newcell itself, because Address converts the Range object into formula text such as A10.

Sub UpdateFormula()

    Dim ws As Worksheet
    Dim newCell As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set newCell = ActiveCell

    If Not newCell.Worksheet Is ws Then
        MsgBox "Select a cell on Sheet1 and run the macro again."
        Exit Sub
    End If

    ws.Range("A7").Formula = ws.Range("A7").Formula & _
                             "+" & newCell.Address(False, False)
End Sub

With A10 active, this changes the formula to:

=A1+A2+A3-A5+A10

The two False arguments produce a relative address (A10). Without them, Address defaults to an absolute address ($A$10):

newCell.Address                     ' $A$10
newCell.Address(False, False)       ' A10

ActiveCell returns a Range object, while Range.Address returns its reference as a string. The Formula property expects a formula expressed in A1-style notation.^1^ ^2^ Directly concatenating newCell invokes the range’s default value, which is why the formula receives 200 instead of A10.

The worksheet check is important because ActiveCell belongs to whichever worksheet is currently active.^3^ Without that check, selecting A10 on another sheet and appending only A10 would make the resulting formula refer to Sheet1!A10, not the selected cell.


References

  1. Range.Address property (Excel)
  2. Range.Formula property (Excel)
  3. Working with the Active Cell
AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

3 people found this answer helpful.
Answer accepted by question author
Marcin Policht 113.6K Reputation points MVP Volunteer Moderator
2026-11-21T23:53:20.4666667+00:00

As far as I can tell, the issue is that newcell is a Range object, and when you concatenate it with a string, VBA implicitly converts it to the cell's value rather than its address.

Use the .Address property to retrieve the cell reference.

Sub UpdateFormula()

    Dim ws As Worksheet
    Dim newcell As Range

    Set newcell = ActiveCell
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ws.Range("A7").Formula = ws.Range("A7").Formula & "+" & newcell.Address

End Sub

If A10 is the active cell and contains 200.00, the resulting formula in A7 will be:

=A1+A2+A3-A5+$A$10

By default, .Address returns an absolute reference ($A$10). If you want a relative reference (A10), use:

newcell.Address(False, False)

So your assignment would be:

ws.Range("A7").Formula = ws.Range("A7").Formula & "+" & newcell.Address(False, False)

The distinction is newcell.Value (the contents of the cell) versus newcell.Address (the cell's reference).


If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.