Edit Text in Formula

[Guest]

Excel Experts,

I want to edit the formula in a cell by appending the existing formula with
a minus sign and the value of another cell.

My spreadsheet is similar to the following:

A B
1
2 Current Position
3 AMD 200 (the formula in B3 is =200)
4
5 New Sells
6 AMD 100

The current code is similar to:

Sub ProcessSell
Set CurShares = Range("B3")

Set TShares = Range("B6")
End Sub

The code I want to add should say,
Edit the formula in CurShares by adding "-TShares"

so that the formula in "B3" will change from "=200", to "=200-100"
and the value of B3 will then be 100.

Thanks,
Alan

 

[Bernie Deitrick]

Alan,

Range("B3").Formula = Range("B3").Formula & "-TShares"
Or--
Range("CurShares ").Formula = Range("CurShares ").Formula & "-TShares"

HTH,
Bernie
MS Excel MVP

 

[Bob Phillips]

Air-coded.

Range("B3").Formula = Range("B3").Formula & "-" & Range("B6").Value

 

[Guest]

Bernie,

Thanks for your assistance.

I edited it a bit and here's what I ended up with.

Alan

Sub Macro1()

Set CurShares = Range("B3")
CurShares.Select

Set TShares = Range("B6")
TShares.Select

CurShares.Formula = CurShares.Formula & "-" & TShares.Value



End Sub