Calculating Subtotal in Excel 2007

[Simran]

Hello,

I would like to create a macro in Excel 2007 that would enable me to do subtotals in a quote (including subtotals but excel does not take into account the overall total).

The sub total is to be done (for example) in Column D. In column E to the line above is the number of cells that are "in Total" , I can do it without any difficulty with the wizard function, but it's hard to do the same thing in VBA.

Basically if I did this :

Dim nbcell As Double
ActiveCell.Select
Selection.ClearContents
Nbcell = ActiveCell.Offset (-1, 1). Range ( "A1"). Value
Nbcell nbcell *= -1
ActiveCell.FormulaR1C1 = "= SUBTOTAL (9, R [-3] C: R [-1] C)"

can you tell me how to solve this problem , please help me thanks in advance

[JonathanD]

Good evening,

If I understand I will suggest this:

Sun nbcell%
Nbcell = ActiveCell.Offset (-1, 1). Value
ActiveCell.FormulaR1C1Local = "= SUBTOTAL (9, L (" &-nbcell & ") C: L (-1) C)"

I took the liberty to prune a little code.

On the other hand I would have kept your formula simple , but this does not solve your problem

[Simran]

Thank you for your help

Here's what happens:

Sun nbcell%
Nbcell = ActiveCell.Offset (-1, 1). Value
ActiveCell.FormulaR1C1Local = "= SUBTOTAL (9, L (" &-nbcell & ") C: L (-1) C)"


Nbcell is well worth the search is going well but it only pulls Active cell



This is my last attempt:

On Error Resume Next
Sun nbcell% Low% High%
Sun SBAS As String
Sun Shaut As String
Nbcell = ActiveCell.Offset (-1, 1). Value
cell = Right $ (ActiveCell.Address, Len (ActiveCell.Address) - 3)
Bottom = cell - 1
Top = cell - nbcell
SBAS = LTrim ( "G" + Right $ (Str $ (Netherlands), 2))
Shaut = LTrim ( "G" + Right $ (Str $ (High), 2))
Selection.ClearContents
ActiveCell.FormulaR1C1Local = "= SUBTOTAL (9;" SBAS & & "" & Shaut & ")"
Stop

The return value is good. It gives "= SUBTOTAL (9; G36: G32)" By cons in the cell is = SUBTOTAL (9, 'G36': 'G32') This function in total is important to me because I'm extending estimates (sub totals will not add to the total)

[JonathanD]

Try it:

On Error Resume Next

Sun nbcell% Low% High%
Sun SBAS As String
Sun Shaut As String
Nbcell = ActiveCell.Offset (-1, 1). Value
cell = Right $ (ActiveCell.Address, Len (ActiveCell.Address) - 3)
Bottom = cell - 1
Top = cell - nbcell
SBAS = "G" & Lower
Shaut = "G" & Top

ActiveCell. FormulaLocal = "= SUB. TOTAL (9;" Shaut & & "" & & SBAS) "