Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Subtotal TotalList=


Quote:





Originally Posted by RichS


Hi Rick,

I had actually caught that, and renamed the variable, but it still did not take the variable.

I have placed variables into Excel command arguments before, but this one doesn't seem to take a a variable...

Again, I'm at a loss...

Thanks for your reply Rick.




http://www.xtremevbtalk.com/showthread.php?t=165820

Has this post been answered anywhere else since this question was asked or is it one that hasn't been worked out yet?

I have come across it because I also need to be able to subtotal using vb on a set of data with variable number of columns.

Below is the code I have been trying but as with RichS and Rick before, have been gettin errror 1004 message up.
The message box that I have put in there shows that strTotalList gives a result of the columns I want to subtotal

Code:
Dim strTotalList(100) As Variant

intColNum = 10 'start at column 10
strCellValue = "TEST"
Do Until Trim(strCellValue) = ""
intColNum = intColNum + 1
strCellValue = Cells(1, intColNum).Value
If Trim(strCellValue) <> "" And intColNum >= 5 Then
strTotalList(100) = strTotalList(100) & CStr(intColNum) & ", "
End If
Loop
strTotalList(100) = Mid(strTotalList(100), 1, (Len(strTotalList(100)) - 2))

MsgBox strTotalList(100)

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Number of columns will be variable here so need to have variable Array
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(9, strTotalList), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True ' I want column 9 then 11 onwards

Can anyone help?




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Subtotal TotalList=
Hi,

I am working from within Access, and I'm exporting a query to Excel, where I then format the query to include subtotals on every column from the fifth column to the last column.

There will be a different number of columns every time I run the Access VBA code, and I have to find a way to place a variable value in the Subtotal command to tell Excel which columns to subtotal on.

My code (which worked on the first running), looks like this:


Code:
xlApp.Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, _
9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
The second time I ran it (using different data, which produced 21 columns as opposed to the 29 columns listed above), I got a runtime 1004 error ("Subtotal method of Range class failed").

I realized that I am dealing with less columns this time so I added the following code to try to create a string that I could place after the TotalList:= argument:


Code:
strTotalList = ""
intColNum = 0
strCellValue = "TEST"
Do Until Trim(strCellValue) = ""
intColNum = intColNum + 1
strCellValue = xlApp.Cells(1, intColNum).Value
If Trim(strCellValue) <> "" And intColNum >= 5 Then
strTotalList = strTotalList & CStr(intColNum) & ", "
End If
Loop

strTotalList = "Array(" & Mid(strTotalList, 1, (Len(strTotalList) - 2)) & ")"
This returned a value for strTotalList of "Array(5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25)", and I placed that variable in the TotalList argument like so:


Code:
xlApp.Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=strTotalList, _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

It still errored out, so I tried to create an array dynamically to throw in there, but to no avail.


Any ideas?

Thanks,

RichS

Subtotal A Subtotal For Excel
Hello to all. Quick question, I'm trying to subtotal a subtotal(as the title says). I keep getting an error on the second line. I have vb6 and excel 97.

Code:
.Range("A1:H" & NewRow - 1).Subtotal 1, xlSum, Array(4, 5, 6, 7, 8)
.Range("B1:H" & NewRow).Subtotal 2, xlSum, Array(4, 5, 6, 7, 8), False, False 'Error is here


Error Message: Subtotal method of Range class failed

I am able to do it in excel manually, but not here.

Any help would be greatly appriciated.

Subtotal
Hello, I have integrated an access database with a VB application thus far and I am interested in making a form which has subtotals..

If I have 4 subtotals (1 field) how can I add them all together?

Eg:

Record 1
Subtotal = $105

Record 2
Subtotal = $200

Record 3
Subtotal = $400

Record 4
Subtotal = $80

Basically I want a subtotal, so I can get $785 which is the sum of the above numbers.

P.S How can I make a data form chart? using the MS Chart component? When I create a bar graph I get a whole heap of bar graphs instead of the little few that I want.

Subtotal
I'm trying to subtotal at the bottom of column a,b,c,d(at line insert). I'm having difficulty identifying the range, since it is changing with n. Any idea?


For n = 3 To 999
Cells(n, 1).Select
If (Cells(n, 1)) = "" Then


Selection.PasteSpecial paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

different_month = DateDiff("m", CDate(Cells(n, 5).Value), CDate(Cells(n - 1, 5).Value))

Else: GoTo 12
End If

If different_month <> 0 Then

Selection.Insert Shift:=xlDown
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With

Cells(n, 6).Select
ActiveCell.FormulaR1C1 = "Monthly Total"


ActiveSheet.Cells(n, 1).Formula = "=Sum(a1:a5)"

Exit For
End If
12 Next n

Something Challenging With Subtotal
Dear Friends

Recently I have come across a excel worksheet from One of my colleague not here with me anymore - I refered his worksheet and found subtotal applied in a Horizontal Format - I know very few of us will be knowing this as I myself have been trying recently as how was this formula or the function used and applied.

Please have a look at the Excel WorkSheet You'll understand what i am talking about and request your Xtreme advice - Thanks

Using Subtotal Function In VBA
Hello everybody

I am trying to read from a worksheet cell, programmatically, which is displaying a value of the Subtotal function.

I used the
Code:
Cells(R, C).Value
statement. Unfortunately, it does not return the value of the formula.

Then I tried to embed the formula within my VBA procedure as below:

Code:
Var = Application.WorksheetFunction.Subtotal(3, V4:V450)
; which results in error
or

Code:
Var = Application.WorksheetFunction.Subtotal(3, Range("V4:V450")
; which returns 0.

I desperately need help to get around the problem. Please help.

Regards

Subtotal Xl2002
Ok,


That works but it is very slow to recalac as I have a big sheet

160 col x 525 rows

if formula in most cells

All worked well until ran on a 2002 workstation

Is there any way to install the XL 2003 addin functions onto a XL 2002 so as to have the xl2003 subtotal extended functions on xl2002

Clutching at straws here!

Any thoughts welcome

Thanks

Computing Subtotal
The code is listed below and I'm not sure how to introduce a subtotal at one of the line insertions. I'm sure that it needs to be placed @ the if <>0 statement, but I'm not sure how the subtotal formula is drafted.
n = 3
For n = 3 To 999

If (Cells(n, 1)) = "" Then

Selection.PasteSpecial paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

different_month = DateDiff("m", CDate(Cells(n, 5).Value), CDate(Cells(n - 1, 5).Value))

Else: GoTo 12

End If

If different_month <> 0 Then

Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown

Cells(n, 6).Select
ActiveCell.FormulaR1C1 = "Monthly Total"
Cells(n, 1) = Subtotals(3, A1, A10) 'this is obviously wrong'
End If
End If
Exit For

12 Next n

Subtotal Question
I want the subtotal formula to update itself when I add new rows to the bottom of the worksheet. What formula should I add?

In cell F1=SUBTOTAL(2,A6:A293)

Thanks

Subtotal Problem
My code won't work Can someone help me with this one.


I have this:

Itemnumber name qty
(column a) (b) (c)

12345 aaaa 2
12345 aaaa 4
12345 aaaa 1
12346 bbbb 4
12347 cccc 3
12347 cccc 2
12477 dddd 1
12478 eeee 2
12478 eeee 3

I need this:

12345 aaaa 7
12346 bbbb 4
12347 cccc 5
12477 dddd 1
12478 eeee 5

Self-Replacing Subtotal
I have code that subtotals 4 different categories. The names are in the first column, and the data that I want to be subtotaled is in the third column. I press "CTRL j" to get the subtotal. If I press it again, it adds a new line instead of replacing the old subtotal. Here is the code, thank you in advance.

Sub DoSubTotal()

' Keyboard Shortcut: Ctrl+j
' This macro subtotals a list when there is more than one qualifier

Dim rng As Range
Dim k As Integer
Dim kntups As Integer

Set rng = Range("A:A")
k = 1
kntups = 0
Do While rng.Cells(k).Value <> ""
Do
If rng.Cells(k) = rng.Cells(k + 1) Then
kntups = kntups + 1
k = k + 1
Else
If kntups >= 1 Then
With rng
.Cells(k + 1).EntireRow.Insert
.Cells(k + 1) = "Subtotal " & .Cells(k)
.Cells(k + 1).Offset(0, 2).FormulaR1C1 = "=SUM(R[-" & kntups + 1 & "]C:R[-1]C)"
End With
kntups = 0
k = k + 2
Else
kntups = 0
k = k + 1
End If
End If
Loop Until kntups = 0
Loop


End Sub

How To Insert Subtotal
based on created Formula in Crystal Report 8.5.

thanks in advance

Find Subtotal For Each Section
Good Day Experts

I have a table that looks like this:

Code:
3. Frequency: Suburbs
Suburb Street Total
Sector 1
Basia Scukum 1
Wellington Sk 1

Sector 2
Davie Kannerie 1
Kuyler Lamini 2

Sector 3
Overaim Fouche Place 1
This is a table that is created when a user clicks on a button. I need to create sub totals for each sector. The following code which I'm using is not doing the trick. Where am I going wrong. Thanks for all the help.


Code:

Sheet11.Activate
Range("A14").Select
Do While ActiveCell.Value <> "Total"
If ActiveCell.Value <> "" Then
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(0, 2).Select
ActiveCell.Font.Bold = True

ActiveWorkbook.Names.Add Name:="startsum", RefersTo:=ActiveCell

Range(ActiveCell, ActiveCell.Offset(-1, 0).End(xlUp)).Select
Range("startsum").Value = Application.Sum(Selection)
Range("startsum").Select
ActiveCell.Offset(0, -2).Select
ActiveCell.Offset(1, 0).Activate

End If
Loop
Kind Regards

Subtotal Rows - How To Identify
Hello,
I am writing a VB routine. I need to be able to differentiate which rows are "subtotal rows", and which are not, so that I can operate on just the "subtotal rows". In my example, I show that row 7 is a "subtotal row", and rows 11, 30, 38, 96, 101. Also, I have shown as visible, rows 3-6, just to show that I still need to identify that row 7 is the first "subtotal row".

Is there a "flag" associated w/ "subtotal rows"?

Subtotal - 2 Criteria / Row Change
Hi, I'm trying to create a subtotal based on 2 column's row changes. Example of my data:

Code:
Country In Country Out Amount
AAA CCC 10
BBB CCC 11
BBB AAA 12
BBB DDD 13
BBB DDD 14
How can I get it to look like this:

Code:
Country In Country Out Amount
AAA CCC 10
Subtotal CCC 10
Subtotal AAA 10
BBB CCC 11
Subtotal CCC 11
BBB AAA 12
Subtotal AAA 12
BBB DDD 13
BBB DDD 14
Subtotal DDD 27
Subtotal BBB 50
Just a thing to note: The first 2 rows have the same Country Out - 'CCC'. Need to avoid having only 1 subtotal instead of 2 (1 each for Country In - 'AAA' & 'BBB')

How To Clear A Datafile And How To Do A Subtotal.
G'day guys I need help again. For the assesment I need to clear the textfile after i've used it so what code would allow me to do this?

Also I need to do a sub-total on the text box that I have How would I do this??

Display Grandtotal From Subtotal Help
hello,please help.how to display the grangtotal from subtotal.

this is the current code:
 Set conDataConnection = New Connection
      conDataConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & _
                "Overtime.mdb;Persist Security Info=False"
   
      Set rs = New ADODB.Recordset
    strSQL = "select Department,WorkDescript, SUM(OtHours) As OtHours from Overtime where OvertimeDate BETWEEN #" & start & "# and #" & enddate & "# Group by Department ,WorkDescript "
          
   
rs.Open strSQL, conDataConnection, adOpenStatic, adLockOptimistic

While Not rs.EOF
        If IsNull(rs("OtHours").Value) Then
            total = total + 0
        Else
            total = total + rs("OtHours").Value
        End If
        rs.MoveNext
    Wend
   
'set the database source in rs
   rptalldept.Sections("Section5").Controls.Item("Label15").Caption = Format(total, "#,##0.00")   rptalldept.Sections("Section4").Controls.Item("Label19").Caption = start   rptalldept.Sections("Section4").Controls.Item("Label21").Caption = enddate
   Set rptalldept.DataSource = rs
    'then set the field
    rptalldept.Sections("section1").Controls.Item("Text1").DataField = "WorkDescript"    rptalldept.Sections("section1").Controls.Item("Text13").DataField = "OtHours"   rptalldept.Sections("section1").Controls.Item("Text4").DataField = "Department"
rptalldept.Sections("section1").Controls.Item("Text5").DataField = GrandTotal
   

thanks for help.
http://i242.photobucket.com/albums/ff158/monchichi2/1.jpg

How To: Get The Master/detail Subtotal

Master table fields:
RefID  RefType  RefNum  RefDate  Branch_Code, Particulars

Detail table fields:
RefID  EmpID  ReasonCode  Debit  Credit

In my report (using DED/DRD), i want to achieve the format like:
RefDate   RefType   RefNum   Amount

Where Amount is the total amount computed from the Detail table.

How do i build my SQL statement in my command object?

TabControl And Subtotal Question
I have two problems that I am faced with that are inter-related.

I have a main Form with a tabcontrol (tabMain) on it with 3 tabpages. Each one of the tabpages on the tabMain have additional tabcontrols i.e On tabMain there is a page for Orders, and on that Orders page I have a tabcontrol (tabOrdersMain) that has 2 tabpages (tabOrdersOrder & tabOrdersDetails) the tabOrder has the necessary information and the tabDetails has mainly pricing details. My problem is that when the form loads the textboxes that are bound to the same datasource don't seem to populate with the data until the tabpage is selected.

I tested it by adding a procedure to the textchanged event for my txtBaseRate textbox and when the form firsts loads the event is not fired, however after I click on the tabOrdersDetails tab the event is fired and is fired there after, but only after the tabOrdersDetails had been clicked on at least once.

Is This normal? Can I do something so that the user doesn't have to click on the tabOrderDetail tab?

My next question is what is the best way to run a subtotal of curreny amounts? For instance, when a user changed a value on my tabOrdersOrder page it cooresponds by updating a rate on the tabOrdersDetails page, which in turn needs to re-summarize the totals, which then needs to display only the summary on the tabOrdersOrder page.

Help would sure be appreciated on these questions. I hope I gave you enough information to evaluate and reply, If not please let me know.

Thanks in advance.

How Do I Display Subtotal On Form?
I'd like to display a subtotal (in a text box I'm thinking - let me know of a better control) that updates total price as the customer selects or unselects various items on the form.

Display Grandtotal From Subtotal Help
hello,please help.how to display the grangtotal from subtotal.

this is the current code:
Code: Set conDataConnection = New Connection
      conDataConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & _
                "Overtime.mdb;Persist Security Info=False"
   
      Set rs = New ADODB.Recordset
    strSQL = "select Department,WorkDescript, SUM(OtHours) As OtHours from Overtime where OvertimeDate BETWEEN #" & start & "# and #" & enddate & "# Group by Department ,WorkDescript "
          
   
rs.Open strSQL, conDataConnection, adOpenStatic, adLockOptimistic

While Not rs.EOF
        If IsNull(rs("OtHours").Value) Then
            total = total + 0
        Else
            total = total + rs("OtHours").Value
        End If
        rs.MoveNext
    Wend
   
'set the database source in rs
   rptalldept.Sections("Section5").Controls.Item("Label15").Caption = Format(total, "#,##0.00")
   rptalldept.Sections("Section4").Controls.Item("Label19").Caption = start
   rptalldept.Sections("Section4").Controls.Item("Label21").Caption = enddate
   Set rptalldept.DataSource = rs
    'then set the field
    rptalldept.Sections("section1").Controls.Item("Text1").DataField = "WorkDescript"
    rptalldept.Sections("section1").Controls.Item("Text13").DataField = "OtHours"
    rptalldept.Sections("section1").Controls.Item("Text4").DataField = "Department"
    rptalldept.Sections("section1").Controls.Item("Text4").DataField = GrandTotal
 
  

thanks for help!

Subtotal Add-in For Excel 2000
I want to sub-total values in one column based on the value in another column. I don't want to use the native excel Subtotal menu function because it's messy with all the key field sub-headings etc.
If you look at the screen shot attached
I basically want to use create an array function that will subtotal the HOURS by REMARKS
So I would start off with Column A and B and using the function end up with column D and E
You can assume that the original list will be sorted alphabeticaly by REMARKS
Does anyone know of any neat add-in that can do that?
I've already found a great VSORT add-in on this site

Excel Subtotal GroupBy Parameter???
Hello I have looked around but do NOT understand what this parameter is and why I need to use it. I thought subtotal should simply be Subtotal(function, range)

help is appreciated and please show example, thanks

Subtotal Calculation Using Excel Macro
Hello,

I have been trying to create an excel macro that should do a subtotal of some selected fields in my worksheet. The worksheet table looks something like:

CLIENTORDER NOSALES H1SALES H2
abcd1234100300
abcd1235200400
abcd1240500100
mnop1212600700
wxyz1300100200
wxyz1315300400
wxyz1325500600
wxyz1335700800

What the macro should do is to do a subtotal of the sales in H1 and H2 against each of these clients. That is, the output of the macro should be like:

CLIENTSALES H1SALES H2
abcd800800
mnop600700
wxyz16002000

I know how to do it manually using the SUBTOTAL function, but I need to implement this feature in a macro, which I am not able to do. Could anyone kindly provide me with some assistance please.

Cheers.

Inserting Subtotal Without Removing The Current One
I have to create 2 subtotal but I dont want to remove the current one....

so basically I want to create more than 1 subtotal


How to achieve this in VBA EXCEL...

I have used the macro recorder but it doesn't help me because it automatically remove the current subtotals


Thx

Need Help - Subtotal, Sum, Addition Doesnt Seem To Work!
Harlow

Happy New Year!

I have a big problem in my little project for my Behaviour Base Safety (BBS) excel VB program. I m basically "wet behind the ears" guy. First time doing VB, hence, pardon the silly questions.

In this BBS, im trying to input raw data obtained into a "form" to be stored as an excel database for future reference and reporting. All goes smoothly till the calculation part of %safe or %risk which returns "#Value!" or just "0"

What I did

1. I create a table to contains all the safe and risk behaviours in a worksheet named "data"
2. I made a user-form containing the following part codes, which i suspect leads to the error.

btw, i use text-boxes to key in the raw data - read somewhere that this method is better... hence, "txt" stand for the text-box

Code:
Option explicit
Dim txt_safe_handnfoot as integer, txt_risk_handnfoot as integer
Dim txt_safe_ppe as integer, txt_risk_ppe as integer
.
.
.
etc till

time to check the individual form's safe & risk cells for non-integer value.

an example of the check for safe & at-risk behaviour.

Code:
Private Sub txt_safe_headnfoot_Change()
If txt_safe_headnfoot.text <> "" Then
If IsNumeric(txt_safe_headnfoot.text) Then
safe_headnfoot = CInt(txt_safe_headnfoot.text)
Else
MsgBox "Wrong Type Used! Must be Integer!", vbExclamation
txt_safe_headnfoot.text = ""
txt_safe_headnfoot.SetFocus
End If
End If
End Sub

Private Sub txt_risk_headnfoot_Change()
If txt_risk_headnfoot.text <> "" Then
If IsNumeric(txt_risk_headnfoot.text) Then
risk_headnfoot = CInt(txt_risk_headnfoot.text)
Else
MsgBox "Wrong Type Used! Must be Integer!", vbExclamation
txt_risk_headnfoot.text = ""
txt_risk_headnfoot.SetFocus
End If
If txt_risk_headnfoot.text >= 1 Then
txt_safe_headnfoot.text = 0
End If
End If
End Sub
i did the "if txt_risk_headnfoot.text >=1 then txt_safe_headnfoot.text = 0" cos if there is any at risk behaviour, the safe behaviour has to be reset to 0.

then after checking that every text-boxes are entered correctly, time to transfer to "data" worksheet before to main database. hence

Code:
Sheets("data").Select
Range("g6") = txt_safe_headnfoot
Range("h6") = txt_risk_headnfoot
Range("i6") = txt_safe_eyesnface
Range("j6") = txt_risk_eyesnface
Range("k6") = txt_safe_hand
Range("l6") = txt_risk_hand
Range("m6") = txt_safe_respiratory
Range("n6") = txt_risk_respiratory
Range("o6") = txt_safe_hearing
Range("p6") = txt_risk_hearing
.
.
.
etc.

now the big problem, say, i dont enter anything under txt_safe_hand or even txt_safe_respiratory, the "=subtotal(9, cell1:cellx)" or even "=cell1+cell2+cell3+..." can NOT WORK!

please do help me solve this problem. in my opinion, the problem could lie on the non-numeric value checking, but i need this little sub program to make the data entry error-free

thx alot and have a great new year!

Min Jie

SUBTOTAL XL2002 Hidden Cells
Excel 2003 has the function called "Subtotal" as does excel 2002 but excel 2002 does not have the additional references that allow the subtotal to exclude hidden cells from a sub tot.

I have written a large spread sheet that uses this function a lot and need to simulate the 2003 "Subtotal" not adding hidden cells on a excel 2002 workstation.

I have written a small function which has a syntax error which if some could advise me on would be great.

Also if any one knows a 2002 summing formula which will not tot hidden cells that would be even better.


Code:
Function EWSUBTOTAL(Fuction_ref, SelectedRange)
'SUBTOTAL(109,D9 : D511) *original Excel 2003 formula to emulate

Dim Rng As Range, a As Range
Dim InRange As Range
Dim Subtotals, Celltotals
'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual

Set InRange = Range(SelectedRange) '* Problem line * Selectedrange should = "D12 : D512"

For Each Rng In InRange
If Not Rng.Rows.Hidden = True Then
If Not IsEmpty(Rng.Value) = True Then
If IsNumeric(Rng.Value) = True Then
Celltotals = Rng.Value
Subtotals = Subtotals + Celltotals
End If
End If
End If
Next Rng

EWSUBTOTAL = Subtotals
'Application.Calculation = xlCalculationAutomatic
'Application.ScreenUpdating = True
End Function

Any advice welcome

SUBTOTAL ERROR: Cannot Determine Which Row In Your List ...
hi everyone,

i`m glad i found this forum. well, i need some urgent help if possible.

i have this subtotal macro, which after running displays the following error: "cannot determine which row in your list or selection contains column labels"

the work flow is that various spreadsheets are copied into one new worbook. After those are copied on the new workbook, the subtotal macro targets only one sheet"DEMO SHEET".

the "DEMO SHEET" has 17 columns and 151 rows. the 1st row is already differently formated (has column-text-labels), which i want to use as header (range is A1:Q1)

whats happens is when i run the subtotal macro, the first row (range is A1:Q1) is moved automatically at the very bottom and thus the 2nd row becomes 1st. After that i get the error message:"cannot determine which row in your list or selection contains column labels".

If i press "OK" it uses the 2nd row as a header for the subtotal.

here is the subtotal macro:


Code:
Sub Subtotal2()
Dim count As String
Dim a As Integer
Dim b As Integer
Dim c As Integer


Columns("J:J").Select
Selection.Style = "Comma"

a = 1
Do
a = a + 1
count = Sheets("DEMO SHEET").Range("b" & a)
Loop Until count = "S"

b = 1
Do
b = b + 1
count = Sheets("DEMO SHEET").Range("b" & b)
Loop Until count = ""
b = b - 1





Range("R" & a).Select

ActiveCell.FormulaR1C1 = "=-RC[-8]"
Range("R" & a).Select
Selection.AutoFill Destination:=Range("R" & a & ":R" & b)


Range("R" & a & ":R" & b).Select


Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Cut
Range("J" & a).Select
ActiveSheet.Paste
Cells.Select


Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(10), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=False


c = 1
Do
c = c + 1
count = Sheets("DEMO SHEET").Range("j" & c)
Loop Until count = ""



Selection.End(xlUp).Select
Range("R3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-16]="""",IF(RC[-8]>0,""BB"",""SS""),"""")"
Range("R3").Select

Selection.AutoFill Destination:=Range("R3:R" & c), Type:=xlFillDefault
Range("R3:R" & c).Select


End Sub

thank you in advance for your time and help!

P.S: i found a similar post on this forum, but i wasn't able to fix this problem


cheers,

Crystal && Adding Subtotal Fields.
Hello all

I have managed to write code that allows a user to decide on the sort fields for a crystal report and then to choose whether the report should group by the first sort field or not.

I would like to be able to add summary fields into the group footer but I am having problems. I have been going around in circles for a few hours now. Anyone managed to do this?

VB6 using DAO (I know DAO is old and outdated).
Crystal Reports V7.


Code:
'-Set the report sort fields object.
Set srtFields = m_rptReport.RecordSortFields
Adding sort fields is done like this:
Code:
srtFields.Add crAscendingOrder, "{" & strTTX & "." & m_strSortArray(lngI) & "}"
The group is added like this:
Code:
m_rptReport.AddGroup 1, "{" & strTTX & "." & Label2(lngI) & "}", crGCAnyValue, crAscendingOrder

m_strSortArray(lngI) & Label2(lngI) hold the field names and strTTX the database. Everything works fine but I cannot seem to figure out how to insert a sub total field!


Thanks

Excel Subtotal - Dynamic Array
Hello,

I'm writing an application that transfers data to a spreadsheet. I need to make subtotals in the spreadsheet with dynamic array's.
Example : the array can be all combinations from 1 to 9 :
array(1, 5, 7, 8, 9)
array(2, 3, 4, 5)
...
I tried to pass an array to the subtotal function of the type variant, that does not work.
I also tried to pass a variant containing the dynamic array : "array(1, 5, 8)", that does not work either.
Anyone an idea how to solve this problem ?

Thanks !

How To Find Subtotal Of Calculated Field?
Hi all,
It is a doubt from crystal reports. How can I
find Subtotal for a calculated feild named Amount?

Amount is sum(feildX) * fieldY

Calculated field is not shown in subtotal option
list....

Is it possible then how?? Please help me...

Sujith Kumar

URGENT :how To Use Subtotal Function In Excel
hi,
platform VB.NET with EXCEL 2000

I HAVE CREATED A EXCEL SHEET THROUGH VB.NET
AND FILLED WITH LOT OF DATA.
NOW I WISH TO USE THE SUBTOTAL FUNCTION.

ANYBODY CAN SEND ME A EXAMPLE?
thank u.

Excel- Automating A Subtotal Function?
I'm trying to automate a Subtotal function, ie, I want to SUM the data in columns C onwards for each change in column A:
Code:Private Sub SubTotalSummaryData(LasDateCol As Byte)
    Dim DataArray() As Byte, cnt As Byte, i As Byte
    
    With Worksheets("Summary")
        ' Populate array of all used columns of data- used by the Subtotal function.
        ReDim DataArray(LasDateCol - 2)
        cnt = 0
        For i = 3 To LasDateCol
            DataArray(cnt) = i
            cnt = cnt + 1
        Next i
        .UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(DataArray()), Replace:=True, PageBreaks:=False, SummaryBelowData _
            :=True
    End With
End Sub


The number of columns present will vary each time the procedure is called thus I tried creating an array of all the used columns via the FOR....NEXT loop, but VBA doesn't like it.

|
+--JDMils
|
+--VB6
+--VB Dot Net
|
+-- Navman GPS Forums @ http://forum.jdmils.com
|

Subtotal Level Change On Protected Sheets
Hi you all,

I have an Excel spreadsheet with subtotals. I protected this sheet to avoid other users from modifying it, but I want them to be able to look at t and if necessary to change the subtotal level. The sheet protection avoids any change of the spread-sheet but also avoids any change in the subtotal level.

Does anybody have an idea of how I could achieve this protection?

Many thnaks for your answers.

Why My SubTotal In Datagrid Not Refresh? (need To Re-open Form)
Hi,
I have combobox on datagrid in the form. In the datagrid, i have FoodName, Price, Quantity and SubTotal. The combobox will show list of food. If i select FoodName..the Price should appear automatically. And i need to enter the Quantity and the value of (Price * Quantity) will be stored in SubTotal. My problem is, i can select the FoodName and enter the number of Quantity. But i can't view the Price and SubTotal until i close the form and re-open it. Is there any way to refresh the datagrid without close and re-open the form?
Any idea? Thanx in advance..;)

Visual Basic - VSFlexGrid - Sorting A Row Having Subtotal.
Visual Basic - VSFlexGrid - Sorting a row having Subtotal.
----------------------------------------------------------------------

I need to sort the rows of the VSFlex Grid which has subtotal.

I am able to use the sort properties but it only sorts the rows of the subtotal group. What I need is that I should be able to sort both the subtotal row and its children rows.

Thanks

Access 2003 Reports Sections Subtotal And Total
Good afternoon folks.

I am trying to count items for a given date including subtotals and a total.

I have the report set up with the extra section for the Subtotal.

All the subtotals are calculating correctly, except for the last one which equals the Total for some reason.

Any suggestions? I was wondering if it had something to do with running sums.

Thank you,

DAVE

Crystal Report Subtotal Not Working In German Setting ??
Hi,
I am using Crystal reports 8.0 .Report is reading filed values from Temp Table.For Decimal datatype field , its not recognising values after decimal ( eg. In table if value is 16.12 then its coming as 16.00 )

Strange thing is : Values are coming fine when I preview at Design time( 16.12 ). but when report is called from VB , decimal values are not coming(16.00 )

The above problem is not working in German Settings(Standard).
Its working fine both at design time and runtime in English(United States) settings.
Can any one help me regarding this?

Thanks
Suraj

Copyright 2005-08 www.BigResource.com, All rights reserved