SUBTOTAL function

[Marlowwe]

Hello guys, can you please help me ??

I have function to create sum, but I need to modify a little bit.

1) I woul like to change "=sum(" to "=subtotal(9;" but I get error message. What is the problem ?
2) I need put into subtotal selected area. How to do it ?

Working macro:

Sub SUBTOTAL2()
'funguje
Dim myValue As Variant
Dim myValuee As Variant
Dim myValueee As Variant


myValue = InputBox("Cell to put SUBTOTAL: A1", "GleedsTool", "L66")
myValuee = InputBox("First Cell: A1", "GleedsTool", "M50")
myValueee = InputBox("Last Cell", "GleedsTool", "M55")


Range(myValue).Select


ActiveCell.Formula = "=sum(" & myValuee & ":" & myValueee & ")"
End Sub

Error macro:

Sub SUBTOTAL2()
'funguje
Dim myValue As Variant
Dim myValuee As Variant
Dim myValueee As Variant


myValue = InputBox("Cell to put SUBTOTAL: A1", "GleedsTool", "L66")
myValuee = InputBox("First Cell: A1", "GleedsTool", "M50")
myValueee = InputBox("Last Cell", "GleedsTool", "M55")


Range(myValue).Select


ActiveCell.Formula = "=subtotal(9;" & myValuee & ":" & myValueee & ")"
End Sub

 

[shg]

Try replacing the semicolon with a comma, or changing Formula to FormulaLocal, or recording a macro inserting the formula.

 

[Marlowwe]

Hello,

I update macro:

1) add apostophe before formula
2) change sum( -> subtotal(9;
3) Delete apostrophe

But in cell is =Subtotal(9;A1:A10) but I dont know how to add to macro key strokes F2+Enter. Can someone help me, please ?

Sub Sum2SUBTOTAL()
Dim c As Range
For Each c In Selection


'insert before formula '
If c.Value <> "" Then c.Value = "'" & c.Formula


'change sum to subtotal
Selection.Replace What:="sum(", Replacement:="subtotal(9;", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Next


' delete aphostrophe
  Dim Rng As Range, Cell As Range
    Selection.Activate
    Set Rng = Cells.SpecialCells(xlConstants)
    Cells(Rows.Count, Columns.Count).Copy
    For Each Cell In Rng
        If Cell.PrefixCharacter = "'" Then
            Cell.PasteSpecial Operation:=xlPasteSpecialOperationAdd
        End If
    Next Cell
    Application.CutCopyMode = False
  
End Sub

This dont work

Sub test2()
    Selection.Activate
        SendKeys "{F2}", True
        SendKeys "{ENTER}", True


End Sub

 

[RoryA]

Why don't you try doing what shg told you previously and then this will not be necessary?

 

[Marlowwe]

Because I don´t know how to do it. I am not good at macro programing.

 

[RoryA]

Just change this in your first code:

ActiveCell.Formula = "=subtotal(9;" & myValuee & ":" & myValueee & ")"

to this:

ActiveCell.Formula = "=subtotal(9," & myValuee & ":" & myValueee & ")"

 

[MattH1]

ActiveCell.Formula = "=subtotal(9[COLOR=#ff0000],[/COLOR]" & myValuee & ":" & myValueee & ")"

Make this one change to your code and it should fix your problem. (Referencing your first post, not your subsequent ones.)

 

[Marlowwe]

Just change this in your first code:

ActiveCell.Formula = "=subtotal(9;" & myValuee & ":" & myValueee & ")"

to this:

ActiveCell.Formula = "=subtotal(9," & myValuee & ":" & myValueee & ")"

And

ActiveCell.Formula = "=subtotal(9[COLOR=#ff0000],[/COLOR]" & myValuee & ":" & myValueee & ")"

Make this one change to your code and it should fix your problem. (Referencing your first post, not your subsequent ones.)

Thank you guys, it´s working .

Please can you help me with the modification just change =sum(A1:A2) to =subtotal(9;A1:A2) (with no msg/insert box) only with selection on cell and run macro?

 

[MattH1]

Hi Marlowwe, quite happy to help with the modification but I'm unsure what you mean by "only with selection on cell and run macro". Can you please elaborate a bit more as to the exact modification you would like?

 

[Marlowwe]

Hi Marlowwe, quite happy to help with the modification but I'm unsure what you mean by "only with selection on cell and run macro". Can you please elaborate a bit more as to the exact modification you would like?

Hello,

I will try to explain. I work with a lot of rows and many Sums and with sum function i can make a mistakes (if you have 1000rows and 50 Sum functions).

1) I have a Sum function in cell C7 (every time I have this Sum in different row and Column). You can see it in the 1st picture.
2) than I will select the cell C7 and run macro
3) macro change "=SUM(" to "=SUBTOTAL(9;" with original calculated area C4:C6

Thank you