I have a 3 layered table: all of the data, the subtotals, and then the grand totals (the subtotals added up). I did the subtotal funcation for all of the subtotals and for the grandtotal. The grand totals grabs all of the subtotals accept for one row. All of the 4 totals miss the one subtotal row. I looked at the formula and it is correct and the same as the rest of the subtotals in the table. This row is in the middle of the table and I did check it is included in the reference in the forumla. I do not know how to fix this, or if it is an error?
I use the Subtotal function to sum several columns during the subtotal function. I dont think I can run a subtotal doing the sums and counts. Is there any way to add the number 0f rows in each subtotal? Count? I'm not sure how to go to each blank row (subtotal line) and count/add the number 0f rows in that subtotal?
In the attached Excel file, there is a pivot table.
In the Data part of the table, there are two columns. The second is the exact same field than the first one but expressed as percentage (Field settings/Options/Show field as "% of" + "Total".
The problem is: I don't want to express this field as a % of Total ('Grand total') but rather as a % of Subtotal (e.g. Danemark Total).
I am trying to create a macro which will use the subtotal function to do a count on all the the new loans in column A. The could below is what I have tried but I am getting a run-time error 13 "Type Mismatch" error at the line highlighted in red in the code. How to write the Subtotal function in the macro.
way to do this but i have a sheet that is into 5 - 6 thous rows, in one of the columns (names) i sort it by names and then order it by subtotal for certain values.
What i need to know, is there anyway i can take just the subtotal values out and put onto another spreadsheet without copying and pasting it all as there are lots of subtotals and this would help alot as the other info is not nec. just the subtotal'd info. either that or is there anyway i can highlight the subtotal'd row info in yellow/bold text anything like that that would make it stand out without having to do it manually?
I’m trying to get my sheet so that at each change in month it creates a sum of the value but I want to sum to show up in the subtotal value column.....
What is the best way to add in column AG as a bar next to the current bars. I would want one solid color, and I don't need it brown up between positive, and negative. Also, I would want a 3rd error bar underneath for FX P&L. Looks like if I try to add a new data series they default to xy ranges, and forgot how to reverse. see attached example.
how can i continue adding the numbers even if in one cell got an error value. Sample:
a1=5 a2=5 a3=#div/0! (this could be #name!, #value!, etc.) =sum(a1:a3) returns #div/0! but i want it to return as 10. any clarification n how to do this?
My macro adds a sheet to an existing Excel workbook. However, this only works the first time. If I delete the added sheet and run the macro again I get the above error. The macro below call a second macro (see later )
I am trying to allow a user to select a file and have the only sheet from that file added to the workbook they are working in. When I run this code I get a "Subscript Out of Range" error
Code: Sub importRawData() Dim rawDataSheet As String MsgBox "Please select the unmodified AR Aging Report exported from PFW", vbOKOnly rawDataSheet = Application.GetOpenFilename(FileFilter:= _ "Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Select File") Sheets.Add(Sheets("PWF AR Data"), , , rawDataSheet).Name = "PWF Raw Data" End Sub
I wrote a macro that basically adds 5 lines at the beginning and puts in a heading (don't want to use headers). The macro worked flawlessly, then i decided to add it to my add-in and now I get the compile error "type mismatch" for the following line
Range("A1").Select
- the "A1" is highlighted in particular.
Rows("1:5").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1").Select ActiveCell.FormulaR1C1 = "1" With Selection.Font .Color = -16776961 .TintAndShade = 0 End With.............................
I'm trying to set up a macro that will capture all the unique values in a column, and then create a new sheet for each value. What I've got so far technically works, but it's also adding an extraneous sheet (with a generic sheet name, not the specified name I'm giving the sheets) and also giving me "Run-time error '1004': Application-defined or object-defined error" when it runs.
I'm trying to tweak this code from a previous form I created but I'm getting a compile error message. On my old form...the first box was a combo list box where the person entering data would select a value. On the new form, its a text box where the person will enter the value. I thought I could just change the me.cbo[name].listindex to me.tbo[name].listindex - but that seems to be causing the issue. I'm not sure what I would put after the me.tbo[name]. to get the code to run...
Code:
Private Sub cmdAdd_Click()Dim lRow As LongDim lPart As LongDim ws As WorksheetSet ws = Worksheets("DataNEW")'find first empty row in databaselRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).RowlPart = Me.tboProdCode.ListIndex
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
I've got a problem involving several Dim'd Variables needing to be added up, they're all Dim'd as Variants though as they can be either strings or numbers at any time.
I need a formula (VBA) to add them up (to add their actual numeric values - not just a "1" if they contain a number) and ignore them all together if they contain string values.
I have a userform that I'm using to add data to a worksheet, with the following
Private Sub CommandButton1_Click() Dim OutSH As Worksheet Set OutSH = Sheets("Sheet1")
OutSH.Cells(nextrow2, 1).Value = Surname.Value OutSH.cells(nextrow2,2).value = ID.value OutSH.cells(nextrow2,3).value = Date.value ...... I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.
IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E" Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
I seem to be experiencing a problem using the SUBTOTAL formula. Is it not possible to use subtotal in a running command? ie [A2] = SUBTOTAL(102,A$1:A1) then drag for a couple cells vertically. "0" is displayed for ALL dragged cells.
