gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 657
- Office Version
- 2019
- Platform
- Windows
Hello,
Want to make E:E & F:F = 0, and not delete them. The code works, but I need to make this modification.
Here is the code I'm using. Below is the worksheet. Thank you.
Here is the spreadsheet:
Want to make E:E & F:F = 0, and not delete them. The code works, but I need to make this modification.
Here is the code I'm using. Below is the worksheet. Thank you.
VBA Code:
Sub ClearSold()
'ClearContents w/o removing formula
Dim Rng As Range
Dim Cel As Range
Dim Ws As Worksheet
Set Rng = Range("N9:N26")
For Each cell In Rng
'select the cells First
If cell.Value = "sold" Then
cell.Select
ActiveCell.Offset(0, -13).Select
Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + 14)).Select '.ClearContents
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'rearrange this section
'modify the <>"" ??
For Each Cel In Selection
If Mid(Cel.NumberFormat, 1, 15) = "_($* #,##0.00_)" Then
Cel.Value = 0
ElseIf InStr(Cel.Formula, "=") = 0 And Cel.Value <> "" Then
Cel.ClearContents
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Next Cel
End If
Next
End Sub
Here is the spreadsheet:
The Whole Enchilada.xlsm | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
8 | Symbol | Sector | Type | Shares | Cost | CURRENT | MAX Value | MIN Value | Cost Value | Market Value | Gain/Loss | G/L % | Weight(%) | Expiration | Strike | ||
9 | AMZN | Retail | call | 9 | $ 5.05 | $ 6.50 | $ 6.50 | $ 5.05 | $ 4,545.00 | $ 5,850.00 | $ 1,305.00 | 28.71% | 47.6% | 6/18 | $3,100 | ||
10 | MSFT | Tech Srvc | call | 10 | $ 5.00 | $ 6.00 | $ 6.00 | $ 5.00 | $ 5,000.00 | $ 6,000.00 | $ 1,000.00 | 20.00% | 52.4% | sold | $10 | ||
11 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||||||||
12 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||||||||
13 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||||||||
14 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||||||||
15 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||||||||
16 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||||||||
17 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||||||||
18 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||||||||
ETNA |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G9:G18 | G9 | =IF($F9=0,0,MAX($F9,$G9)) |
H9:H18 | H9 | =IF($H9=0,$F9,MEDIAN($H9,$F9,0)) |
I9:I18 | I9 | =(E9*D9)*100 |
J9:J18 | J9 | =(F9*D9)*100 |
K9:K18 | K9 | =J9-I9 |
L9:L18 | L9 | =IF(K9<>0,K9/I9,"") |
M9:M18 | M9 | =IF(I9<>0,I9/$I$27,"") |
B12:B18,B9:B10 | B9 | =IF(COUNTIF(Sectors!$A$3:$V$50,A9)=1,INDEX(Sectors!$A$2:$V$2,MAX((Sectors!$A$3:$V$50=A9)*(COLUMN(Sectors!$A$2:$V$2)))),"") |
O10:O12 | O10 | =IF($D10>0,$I10/(5*100),"") |
Press CTRL+SHIFT+ENTER to enter array formulas. |