I have a list of data in Excel with Autofilters. At the bottom of the data set is a row that totals up many of the columns. Whenever I use the Autofilters, the totals at the bottom still reflect the entire set of data, rather than only the filtered data.
Is there a way (using macros, formulas, or something else) to make the totals automatically update to only reflect the data that is visible when using Autofilters?
I have a large table which I am filtering 3 times. There should be results but the final VBA filter does not return them until I manually apply the filter. The custom filter is correctly populated with the criteria. The code that I am using is as follows:
VB:
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=9, Criteria1:="<>", Operator:=xlAnd
Selection.AutoFilter Field:=10, Criteria1:="=INV", Operator:=xlOr, Criteria2:="=CRD"
Selection.AutoFilter Field:=21, Criteria1:=">=" & DTPicker1.Value, Operator:=xlAnd
The first two filters work fine. The last does not. Oddly it works on some date picker values and not others, even if there are actual results and as mentioned earlier if I stop the program running and apply the filter (without typing anything else) it works fine.
I want to use the selection from a combobox on a sheet to set the autofilter. I know it is probably very simple but have not been able to find the answer by searching this forum. Below is the code I am using. It sets the filter to blanks for some reason.
Private Sub ComboBox2_Change()
Range("D9").AutoFilter Field:=1, Criteria1:=ComboBox2.Value, visibledropdown:=False
End Sub
I have a worksheet of data that I have created by combining several other workbooks into one using a macro. On this worksheet I run AutoFilter to select certain rows and then copy these rows to another worksheet, again using a macro. How can I get the macro to generate a total for one of the columns? I would like the Sum to be two rows after the end of the column with some kind of descriptor preceeding it.
View 6 Replies View RelatedI have a problem very similar to this thread: [URL] .... Therefore I have tried to adapt but so far failed.
My requirement is that a userform pops up with multiple comboboxes (in this scenario 3) and once the results have been selected and the user clicks the button "OK" then the autofilter changes to the same as what the selected ComboBoxes were.
So, there are 3 comboboxes so I have tried the following:
VB:
[PrivateSub CommandButton1_Click()
Worksheets("Data").Activate
If Sheets("Data").AutoFilterMode = True Then Range("B3", Range("B600").End(xlUp)).AutoFilter
Range("B3", Range("B600").End(xlUp)).AutoFilter Field:=1, Criteria1:=ComboBox1.Value, visibledropdown:=False
[Code] .....
I want to create a searchbox in Excel which will locate text in a massive amount of data, for example, if a user types into the box....
"123"
I want the search box to filter the spreadsheet using the autofilter from cell B3, thus filtering out all results that are NOT "123".
Currently I have a button to press which brings up the CTRL + F screen, but that isn't exactly what is required in this instance.
I want a msgbox to popup if the autofilter returns no records saying "No records found".
Heres a sample of the
Sub cmbFindAll_Click()
Dim strFind As String 'what to find
Dim rfilter As Range 'range to search
Set rfilter = Sheet1.Range("a2", Range("f65536").End(xlUp))
Set rng = Sheet1.Range("a2", Range("a65536").End(xlUp))
strFind = Me.TextBox1.Value
With Sheet1
If Not .AutoFilterMode Then .Range("A2").AutoFilter
rfilter.AutoFilter Field:=1, Criteria1:=strFind & "*"..........
I'm working on a quote template that has 600+ products with descriptions and prices that gets autofiltered down to one product. After it has been filtered down to that one product how do I link that to a new worksheet?
View 9 Replies View RelatedThe error above comes up every time I copy filtered data to a new worksheet. It does its work but the said error comes up.
Sub AUTOFILTER_withouthead()
' AUTOFILTER_for_drop Macro
' Macro recorded 1/27/2008 by DD
Dim ws As Worksheet, wd As Variant
Set ws = Worksheets((Worksheets("Destination"). Cells(1, 6).Value))
Set wd = Worksheets("Destination").Range("A1:F65000") ...
I have a simple three column range. I Autofilter the range based on one of the values in Column 1. I then want to grab the results into a range object.
I've been trying to use the Specialcells(xlcelltypevisible) route to no avail. It only gets one row when I should have many.
I have a sheet that I need to routinely filter for a specific code then paste it into a different sheet in the same workbook. I would love to set up a simple macro that would do this for me, but I can't seem to figure it out.
In the results I would like the header row if at all possible, but I can always just make it part of the macro.
I have created a macro some time ago that is an integrated part of an XLA. The Xla has worked fine but now, for some reason, the macro fails to import the specified text, it doesn't fail but nothing gets imported. I have tried solving this myself, but alas I am not bright enough
The code is:
Sub GetWorksheet()
Dim filetoopen As String
Dim wb As Workbook
filetoopen = Application _
. GetOpenFilename("XL Files (*.xls), *.xls")
On Error Resume Next
I got the following code from Use AutoFilter to filter and copy the results to a existing worksheet and would like to incorporate this into my VBA project. The problem however is that this code were written to perform on one workbook and this is where my problem is. My project is between two different workbooks and cannot seem to get this code modified to do what it is supposed to do between these two workbooks. Everything I have tried so far failed. In short what this code would do is to check the existing data on the one sheet (the source) and extract only the data which is meeting my set criteria, and copy this data to the destination sheet. This is what I would like to do between two workbooks. With this the sample code as provided by Ron de Bruin. The sample workbook could be accessed trough the following link [URL]..... With this the code for matching and copying on one workbook.
Code:
Option Explicit
'>>
'This example will copy the filter results below the existing data on the destination sheet.
'Note the sheet "RecordsOfTheNetherlands" must exist in your workbook.
'This example will not copy the header row each time so when you manual add the worksheet
'"RecordsOfTheNetherlands" to your workbook you must add the headers yourself on the first row.
[Code] ............
I am performing an autofilter within a spreadsheet to display only those lines where a name exists in column A. Then I delete all hidden rows. I am having a problem when the autofilter results in no rows being visible. Here is the code I am using for the delete hidden rows:
On Error Resume Next ' In case there's no hidden cells
With Cells
Set rngHidden = .SpecialCells(xlCellTypeVisible)
.EntireRow.Hidden = False 'Unhide all cells
rngHidden.EntireRow.Hidden = True 'Hide previously visible cells
.SpecialCells(xlCellTypeVisible).EntireRow.Delete 'Delete previously hidden cells
rngHidden.EntireRow.Hidden = False ' Unhide previously visible cells
End With
End Sub
I am trying to sum the results of a filter via VBA but cannot seem to get it right. I have the code to count the number of "active" rows, now to get an average I have been trying to use similar code to get the sum so I can divide the sum by the number of rows not filtered out. I get the error "Unable to get the Sum property of the WorkSheet function class" This is the code I have butchered:
Set R = ActiveSheet. Range("d1"). CurrentRegion
Set R = R.Offset(1, 0).Resize(R.Rows.Count - 1, R.Columns.Count)
For i = 1 To R.Rows.Count
If Not R.Rows(i).EntireRow.Hidden Then
intCountCancelled = WorksheetFunction.Sum("Cancelled") ' cancelled is a named range. This is the line its failing on
End If
Next i
I am having trouble with formula to calculate daily results.I can only calculate results base on daily plan.
please follow this link [URL] .....
Part A
2/12
3/12
4/12
5/12
6/12
plan
0
0
50
50
0
[Code] .....
In the attached sheet, I need to find all items in column D that start with "LAB". For those items, Multiply the corresponding values from column B x column E and return the sum of all to cell T1. Once the total is in cell T1, I need to then delete the values in column E for those items (or change them to zero). I dont even know where to start with this.
View 3 Replies View RelatedI am building an excel workbook to calculate a number of different results and I am having trouble with one particular formula.
I need to enter in a formula that will return a value for the following:
ex. C21 = 10
C22 = 10
C23 = 10
C24 = IF CUMULATIVE SUM OF C21:C23 IS GREATER THAN 15 THEN START COUNTING AT 15 BUT INCLUDE THE FIRST 15
SO USING THE EXAMPLE ....IF C21:C23 = 30 THEN C24 SHOULD EQUAL 30. BUT IF C21:C23 WERE TO EQUAL 14 THEN C24 SHOULD EQUAL 0
I have a speadsheet that has a table containing scores of audits, in the last 3 columns of the tabe I have the two totals (columns AC and AD)
In Column "AF" I wish to show where they will rank based on the results, first I need to rank via the result of column AD and if that is the same as another result use column AC to determine where they rank.
I have in column A a list of investment fund names. In columns B to D is respectively for each fund its Market Value, the Rate of Return and the Rank (1, 2, 3 or 4). I am trying to create a formula that will calculate the market value-weighted rate of return for each ranking.
For example, the 3 funds that rank first have market value weights of 10%, 12% and 13%, and rates of return of 1%, -3% and 5%. The total market-value weighted return for the top-ranked funds would therefore be 0.39% (i.e. 0.10*0.01-0.12*0.03+0.13*0.05)
The formula would first need to look in the ranking column to identify those funds with a particular rank (column D), then calculate their weights by dividing their market value by the total market value of all funds (column B), and finally multiplying these weights by the rate of return (column C), before summing the results to say, for example, that the total market-value weighted return for the top-ranked funds is 0.39%
Just curious to know of any other formulas similar to the "quartile" function that allow you to calculate results in thirds (plus any other if known)
View 2 Replies View RelatedI have a Template which is used to import log data, calculate and displaying the results in several charts. Then all sheets are copied to a new workbook, a Save As dialog apears for the new workbook and the template will close. This is all done with VBA and it works fine.
Problem:
The Worksheets with the Charts on it refer to the data-source at the template file.
Strange because the copied worksheets have the same names as the originals.
(when Opens: This Workbook contains Links to other Workbooks...)
Question:
How can I establish that the Charts will point to their own data sources in the same Workbook rather then linking to the Template file on a Server.
I'm creating an (English) football predictions competition for me and my family.
One problem that has stumped me is how to get the scores based on the 'home' & 'away' score predictions.
The rules are: If I predict the correct exact result I get 3 points. I want to add another 'rule' whereby if I predict the correct winner, I get 1 point. Incorrect predictions get 0 points. I don't know how to do this using a formula.
I want to display the percentage of occupancy for each month for condo rentals.
I have columns with dates that represent bookings throughout the year. check in and check out dates to be more precise. (A1 and B1 for example) and I have columns with months Jan, Feb, etc. (C1, D1, etc. for example)
The problem I run in to is that I can calculate the number of days between the dates and the fraction of the year (*12 to get a monthly value, but I fail to understand how I can break that up so that I can populate each month with a correct percentage of the total term.
E.g. A1=1-jan-2014 and B1=20-jan-2014 is an easy one because it only covers 1 month:
formula: =(YEARFRAC(A1,B1,3))*12 gives me 62,47% which I could just use as is for that month.
So far so good, but when the period covers more than one month or when the months overlaps, I don't know how to break the outcome up into the appropriate months to display the correct percentage for each month in its own column.
e.g. A1=1-jan-2014 and B1=20-Mar-2014 gives me a result of 253% with the same formula.
100% for November, 100% for December and 63% for January, which I could somehow formulate to break up over different Months.
But then it gets more tricky. What if the booking starts on a day other than the 1st of the Month?
e.g. A1=5-jan-2014 and B1=20-Mar-2014
The result of the formula (243%) is correct, but isn't sufficient to put the correct percentages for each month in their respective column.
What formula(s) should I use to break down the percentages to match the correct fraction of each individual month?
I attached an example of what I have so far for your review : test.xlsx
Basically a cell might have a number with 3 decimal places
For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell.
Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items.
I included an example of what i mean in this excel file which should clear up any confusion.
[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.
I'm using dynamic name ranges, as follows, for the detail:
WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)
Other detail data is defined as these examples show:
GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)
All detail data begins in Row 6 in the detail spreadsheet.
In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.
Here are two examples of the calcs I'm using in the summary:
=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")
Can someone tell me what is going on? What I'm doing wrong?
Im sure this is a very common problem. I tried searching for it but I havent found anything that solves this for me. Here is the code Im using:
View 3 Replies View RelatedI can select the top cell in column "F" after filtering by multiple columns using VBA and arrays, but now want to I want to use the top cell in column "F" to search for all other equipment that uses this item.
E.g. remove filter, and reapply autofilter to column "F" based on selected cell as per below VBA
Note: Row 1 contains command buttons and row 2 Headers.
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies View RelatedI can set the autofilter, change it, remove all using Macros. What I need to know how to do is reset a filtered column to ALL while leaving all other filters on.
View 2 Replies View Related