vba modification

gtd526

Active Member
Joined
Jul 30, 2013
Messages
453
Office Version
  1. 2019
Platform
  1. 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.

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
ABCDEFGHIJKLMNO
8 Symbol Sector Type Shares Cost CURRENT MAX Value MIN Value Cost ValueMarket ValueGain/LossG/L %Weight(%)ExpirationStrike
9AMZNRetailcall9$ 5.05$ 6.50$ 6.50$ 5.05$ 4,545.00$ 5,850.00$ 1,305.0028.71%47.6%6/18$3,100
10MSFTTech Srvccall10$ 5.00$ 6.00$ 6.00$ 5.00$ 5,000.00$ 6,000.00$ 1,000.0020.00%52.4%sold$10
11$ -$ -$ -$ -$ -$ -$ -   
12 $ -$ -$ -$ -$ -$ -$ -   
13 $ -$ -$ -$ -$ -$ -$ -  
14 $ -$ -$ -$ -$ -$ -$ -  
15 $ -$ -$ -$ -$ -$ -$ -  
16 $ -$ -$ -$ -$ -$ -$ -  
17 $ -$ -$ -$ -$ -$ -$ -  
18 $ -$ -$ -$ -$ -$ -$ -  
ETNA
Cell Formulas
RangeFormula
G9:G18G9=IF($F9=0,0,MAX($F9,$G9))
H9:H18H9=IF($H9=0,$F9,MEDIAN($H9,$F9,0))
I9:I18I9=(E9*D9)*100
J9:J18J9=(F9*D9)*100
K9:K18K9=J9-I9
L9:L18L9=IF(K9<>0,K9/I9,"")
M9:M18M9=IF(I9<>0,I9/$I$27,"")
B12:B18,B9:B10B9=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:O12O10=IF($D10>0,$I10/(5*100),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

gtd526

Active Member
Joined
Jul 30, 2013
Messages
453
Office Version
  1. 2019
Platform
  1. Windows
I re-arranged the macro, now it works the way I wanted. thanks for the interest.
 
Last edited by a moderator:

Forum statistics

Threads
1,148,173
Messages
5,745,183
Members
423,931
Latest member
thangvan114

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top