Manual Filtering Function Isn't Working
Aug 14, 2014
Screenshot (72).jpg
This is probably a really stupid question, but I can't for the life of me figure it out. I need to do some very basic filtering, but the dropdown box where you check off the things that you want to filter by is not defaulting to show checkboxes. This didn't seem like a big deal at first, but it's made it impossible to filter all but one things. For instance, to filter everything but values that are 0, I would have to manually click every single value in the dropdown box. Clicking "select all" is doing literally nothing. It's this way for all my excel documents. how to get the boxes back? I've included a screenshot of what comes up whenever I click on the manual filter button to show what is coming up.
View 12 Replies
ADVERTISEMENT
Jun 26, 2014
[Code] ....
In column F I have a list of dates (dd/mm/yyyy).
I can manually filter dates ok, but want to set it to part of code to use automation for a report.
The code when run, finds nothing, where it should. I tried using [CDate] also but also same result.
View 3 Replies
View Related
Jan 31, 2014
I'm trying to write a macro that filters a table via textbox (criteria), specifying the column to filter through a combobox. I managed to get it to work with every format (date, text, etc.) except with numbers. I'm attaching the file so you can take a look at the code.
If I have a column containing numbers but the cells have text format and I reformat those cells to numbers (using points to separate thousands [I'm from Venezuela, we use dots, not commas]) the results aren't visible unless I modify each cell individually. How can I avoid this?
Lastly, I'm using a macro that I found online that sets invisible shapes on each cell of the header and asigns another macro to these shapes to sort by ascending or descending order in the column over which the shape is put. I made some changes to the macro that actually sorts the values and it works fine, but sometimes I have to resize the shape (on the left side) so that it's further inside the cell or else I'll get an error.
EDIT: It doesn't work with dates either!
EDIT2: I tried copying the table and the codes to a new workbook and now magically it works with numbers, but still not working for dates. Also I'm still having to resize the invisible shape (only in header of the first column ('C')) and the changes in format still aren't visible unless I modify each cell. I think this last issue has something to do with 'SortOneTime' macro or the 'Ordenar' macro because it happens after I run them.
Tabla General (Nueva) (Combobox).xlsm
View 1 Replies
View Related
Apr 27, 2006
I have a large amount of student demographic, course, and class data on an
Excel worksheet for an entire school district. The ethnic code for each
student is a single alpha character. I need to find a way to get a total for
each ethnic code in each course when filtering by each course.
Each column of data has a header and I am using the Auto-filter feature for
filtering. Is there a way that the COUNTIF function can be set up for each
ethnic code so that it will count from the filtered data? If not, is there
any other function, or nested functions, that will work?
View 10 Replies
View Related
Apr 29, 2014
I am looking for a function or a formula that performs the same process in Excel 2010 as:
Data Tab -> Sort & Filter -> Advanced -> *radio button* Copy to another location -> List range: A1:A100 -> Copy to: B:B -> *radio button* Unique Records only.
View 2 Replies
View Related
Aug 27, 2009
I was looking for a final result as follows
21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)
21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)
View 2 Replies
View Related
Jul 9, 2014
I have a dropdown answer column (J) to select an answer and in column (K) next to it that scores the answer. I'm trying to sum the score of (column K) in cell K7 but can't seem to get it to work.
View 3 Replies
View Related
May 1, 2008
I copied this function from a VBA book and I get a Error 91 "object variable or with Block variable not set"
I have made sure that Microsoft scripting is referenced.
any clues would be appreciated.
I might add that this is a follow up on a thread I discussed with "Datasmart", thanks to John for getting me started.
{code]
Function BrowseForfolderShell() As String
Dim objshell As Object, objfolder As Object
Set obshell = CreateObject("Shell.Application")
'Uncomment next line to start at desktop
'Set objfolder = objshell.BrowseForFolder(0, "Please Select a Folder", 0, 0)
Set objfolder = objshell.BrowseForFolder(0, "Please select a Folder", 0, "c:")
If (Not objfolder Is Nothing) Then
View 9 Replies
View Related
Aug 9, 2012
I have attached a sample file where I want to concatenate data in colums A2:H2 into one column with line breaks. But CHAR(10) function alonwith CONCATENATE is not working. I have formatted the destination cell for wrap text.
View 9 Replies
View Related
Feb 16, 2009
This Trim funcion is not working for me for some reason. I have attached a sample file. Can someone help me with this? You can test the macro to see. I just want to delete the trailing blank characters on Column A. Here is the code.
View 14 Replies
View Related
Sep 26, 2009
I have set of data in which i want to put filter Milestones Type <> Milestone
and a filter Actual date is greater than or equal to 30 days ago and is less than or equal to today.
View 4 Replies
View Related
Dec 12, 2011
I've written a Sub that separates words in a sentence into columns in an excel worksheet and it works perfectly. I can't however convert the convert it to a function procedure. when I do so I receive a Circular referencing problem or upon making necessary corrections to prevent circular referencing, I get the Excel #Value error. Here's the code:
Function TextToColumns(Txt) As String
'
' Enters Text Separated By Spaces Into Columns
'
Dim i As Long
Dim k As Long
Dim cell As Range
[code]......
View 1 Replies
View Related
Feb 27, 2012
For some reason my array called "PriceSum" is not getting load with values or not being summed. I am not sure if it is because I am trying to load a value with a decimal in it or not.
For logic purposes:
lastrow = 2
Stock = Banking
Cell "W2" = 5
Cell "X2" = 71.84
Cell "U2" = Bought
Cell "V2" = Banking
Code:
Private Sub Purchase_Click()Dim MyArray As Variant
Dim x As Integer
Dim t As Integer
Dim lastrow As Integer
Dim QuantityArray(0 To 1000) As Variant,
[Code] ...........
View 9 Replies
View Related
Sep 24, 2012
I've written some code to try to count the number of cells that are not empty and save the result as a variable but sometimes the code seems to think that the cells are empty even though they are not.
The code goes through rows 1 at a time and counts the quantity of cells that are not blank and compares them with a couple other values.
The strange thing is if I execute the actual function =COUNTA(with the range that I am selecting below) the COUNTA function works properly by itself in certain problematic sections of the worksheet.
This code below seems to work correctly most of the time but I've found a few instances where it is not properly counting non-blank cells:
Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(0, 27)).Select
QtyTests = WorksheetFunction.CountA(Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(0, 27)))
why my QtyTests variable sometimes is saved as 0 even though there may be some cells that contain values?
Here is the expanded code:
Sub Macro()
'Global Variables
Dim QtyTests As Integer, ReqTests As Integer, InitQty As Integer, Cork As String, Corktype As String
Dim result As Long, x As Integer
[Code]....
View 2 Replies
View Related
Jan 7, 2013
I have a worksheet with dates on and have user form to display dates within a range. I have created some code, but the find function errors and says it cannot find this value on the sheet, but it is definatly there. This is my code: I have added an asterix to where it errors and says it cannot find the value
Code:
Private Sub SearchButton_Click()
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
Dim FoundFrom As Range
Dim FoundTo As Range
With Worksheets("Calender")
[code]....
View 2 Replies
View Related
May 10, 2014
I got a problem with a macro i'm working on. I got column D which contains text as "Figures", "Toys", "3DS", "PS3" etc. I also got column N where i need the macro to display "Toys" if it's a toy, figure, etc; or "Games" if it's "PS3", "PS4" etc.
I'm using the following code, to also select only blank rows (so to ignore row 1 which is table header). Problem is that the result shows only "Games".
VB:
Columns("N:N").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(RC[-9]=""*toys*"",""Toys"",""Games"")"
View 4 Replies
View Related
Aug 13, 2014
I'm in a workbook, and I want to look up a name. CTRLF or clicking on the binoculars both bring up the expected dialog box, but when I populate "Find What" and either hit enter, click Find All or Find Next, nothing happens.
The only thing I can think of is that I created a macro to function in one sheet only, then saved this workbook as .xlsm. But that doesn't seem right.
View 1 Replies
View Related
Jan 7, 2014
I have the following formula that has ceased to work in excel. I don't think there are any errors in it so I'm unsure as to why the link no longer works. Neither of the files I'm using has moved location on the server.
=IF(H7>0,(I7*H7)+((I7*H7/100)*VLOOKUP(B7,'T:FILEFILEFILE'!$B:$E,4,FALSE))+0.25,0)
I'm not sure if this makes much of a difference but people opening the file use various versions of Microsoft Office.
View 10 Replies
View Related
Jan 27, 2014
I have 3 worksheets for 3 separate managers, each sheet has a column which uses a COUNTBLANK function to work out the number of days off an employee has had off in the last two weeks:
Now the formula works perfectly on each worksheet, however I also have a master worksheet (which is identically set out and formatted to the 3 manager sheets) which uses a vlookup formula to pull the data from all 3 worksheets into one at the beginning of the workbook.
The problem I've come across is that the COUNTBLANK formula doesn't work on the master worksheet - I think this has something to do with the vlookup formula entered into the cells which I consider to be blank, but excel doesn't?
I've tried using a separate vlookup formula to pull the data from the 3 managers worksheets and this does work unless an employee appears on more than one managers' sheet - then the data from both worksheets is added together.....
Is there another function I could use to display the information I need to?
View 1 Replies
View Related
Oct 22, 2007
I attempted to create a user function as per the link that VBA Noob posted in this post http://www.excelforum.com/excel-worksheet-functions/619446-resolved-play-a-sound-when-a-cell-equals-a-certain-value.html
I get #Name error. I placed the wav file Applause.wav in the same folder as the workbook. What did I do wrong?. Also, assuming I get this problem sorted out, how would I represent the condition "pass" or "fail" as in cell B3 in the Alarm formula? Would it be; Alarm(B3,"Pass") or Alarm(B3,"=Pass") or something else?
View 5 Replies
View Related
Aug 7, 2009
Ok, I'm sure that there is something really obvious here, but I'm just not seeing it....
Test Data starting at cell A1:
View 14 Replies
View Related
Oct 14, 2011
My Macro opens an excel file and I've to count the number of blank spaces for every filled column. I'm using CountBlank function for that.
But, the code fails at the countBlank line by saying Type Mismatch.
Code:
Sub Snippet_Code()
Dim apdata as excel.application, wbdata as workbook
set apdata = new excel.application
Set wbData = apData.Workbooks.Open(Filename:="ABCD.xlsx")
For colnum = 1 to 50 'Columns from 1 to 50
[Code] .......
My code fails at the BlnkCnt part. I also tried assigning the two cell addresses in two variables and passing the variables to the range method. But there again, I get the error of Method Range of class _Global Failed.
What might be the error?
View 6 Replies
View Related
Sep 27, 2012
I am trying to use index & match function to extract data from data base with has multiple critera in rows as well as cloumns but it is not giving correct results.
Example:
A
B
C
D
E
[Code].....
In C9 I typed =INDEX($B$2:$E$6,MATCH($A9,$B$1:$E$1,0),MATCH($B9,$A$2:$A$6,0))
The result I got was #Ref!
what formula should I use in C9 to get 20, C10 to get 5 & so on
FB1 to FB4 are column headers (critera)
GL 1 to GL5 are row headers (critera)
View 2 Replies
View Related
Feb 25, 2014
I use this formula in a depreciation schedule to determine months in service.
=ROUND(((DAYS360(G12,$Y$1,FALSE)/30)),0)
G12 is my start date and $Y$1 my end date. Every month I update this to the current period end date and up until now, it has recalculated.
Today, I changed $Y$1 from 1/31/2014 to 2/28/2014. The cells with that didn't update. For giggles, I entered in previous dates, and it worked fine.
Is it because 2/28/14 is a future date? I tried 2/24/14 with no joy...
View 1 Replies
View Related
May 2, 2014
I want to use TextToColumn function of excel using vba. I tried recording a macro to get the syntax and then make a few changes to it; But it is not working when i run it from vba ... if i do it manually it is working very nicely but it just skips that step in vba.
i have pasted my code
Code:
On Error Resume Next
rngReqIDcodWhole.TextToColumns Destination:=rngReqIDcodWhole, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:="""", FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
rngReqIDcodWhole is a column range
the text that the code needs to break is as follows
Code:
_BRE.RFs.DocPackage.Decpages.CreateContent_DECPAGES_RF(dre, sec, "FormID","FormID", 1, "DecR_1", "DecR_1", false);
View 7 Replies
View Related
Jan 7, 2008
I'm new to the DMIN function and I'm having a problem.
Cell A2: Date (rest of column is date format) (heading for the column)
Cell A2: Credit (rest of column is acctg. format) (heading for the column)
Cell A2: Debit (rest of column is acctg. format) (heading for the column)
Cell A2: Balance (rest of column is, equals balance field from previous row + credit - debit for current row, acctg. format) (heading for the column)
Column E: Notes (rest of column is generic text) (heading for the column)
Cell K1: Date (the text "Date")
Cell K2: > TODAY() (the text "> TODAY()") -- maybe this should be ="> TODAY()"?
Cell H2: =DMIN(A2:D1000,"Date",K1:K2)
I'm trying to get the minimum balance for the Balance column where the date is greater than today (lowest balance that I have in the future, which helps me see if I will be overdrafting).
I'm getting a value of 0 in H2. I have also tried =DMIN(A2:D1000,1,K1:K2) and get the same result.
View 9 Replies
View Related
Aug 28, 2008
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.
View 9 Replies
View Related
Jul 23, 2014
I need to compare 2 cell with 1 specification reference.But the function can't give return value as per required.Both 2 input cells using Data Validation List.
Please refer attachment for some examples : matching.xlsx
View 2 Replies
View Related
Jan 6, 2010
I am trying to use the NETWORKDAYS function in excel to calculate the working days for my company. the problem is I m located in UAE and here Saturdays and Fridays are official days off for my company. Now the problem is that the excel have this built in function that Sunday and Saturdays are weekend days. I want to customize the function for my local days off for my company.
View 14 Replies
View Related
Jun 11, 2014
I have created worksheets search box and want to use Trim Function to avoid spacing error when user enter the sheets name they are looking for and also sheets name in the Activeworkbook.worksheets.name
However the trim Function seems does not work
[Code] .....
View 8 Replies
View Related
