I am attempting to set a value in a cell based on the content of another two cells. I want it set the value to 35 or 38 only if the txtProgram.Text contains Math*. Unfortunately, it always changes it to the Math value based on txtGrade.Text. What mistake am I making?
Application.Goto Reference:="SEFirstPass"
If txtGrade.Text Like "K" Or txtGrade.Text Like "1" Or txtGrade.Text Like "2" Then ActiveCell = "27"
ElseIf txtGrade.Text Like "3" Or txtGrade.Text Like "4" Or txtGrade.Text Like "5" Or txtGrade.Text Like "6" Then ActiveCell = "30" End If....................
I have spent the last 2 1/2 hours trying to figure this one out on my own. I have attempted multiple possiblities from the forum, however without success.
Okay, I need a count based on 2 criteria. My data source is a different workbook.
My last attempt on a formula is:
=SUMPRODUCT(('[eTE Status - TX HSP Math 2009.xls]Project'!$B$2:$B$248=D2)*('[eTE Status - TX HSP Math 2009.xls]Project'!$A$2:$A$248="978*"))
Column A - ISBN # This will ALWAYS begin with 978 or will contain N/A. There are no blank cells in this column Column B - Grade
In the formula above, cell D2 represents the Grade criteria. And in the second part of the criteria I was trying to create a wildcard so all numbers beginning with 978. Excel "accepts" this formula with giving me an error message, but yields 0 as a result rather than 34.
I'm trying to write this but it returns a 0 when I know there are 3 records that match this criteria: =SUMPRODUCT(('Invoice-Detail'!J2:J50="NewJob_Post.NET")*('Invoice-Detail'!H2:H50="KY_*")). I think the problem is in the wildcard character. I don't know if I should be using COUNTIF or SUMPRODUCT or something else?
My goal is for any of my team members to be able to use a macro to delete all of the unwanted text from a fairly large worksheet and only show their specific information. I created it for myself and posted a slimmed down version of it below. Instead of the macro deleting any rows that do not have BRANDON SCOTT in column D, I want a pop up box to enter your name, and then any rows that do not have your name in column D will be deleted. I’m sure it is something simple, but I have not been able to find another forum with this information.
Sub MyTeam2() With Columns("D") . AutoFilter field:=1, Criteria1:="<>BRANDON SCOTT*" .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete .AutoFilter End With End Sub
I am trying to make an "intelligent" auto filter that with filter with increasing restriction until a certain criteria is met.
The list runs from A5:G20. In coloumn G is the number of hours associated with each event. And in A1 I have the percentage of items showing/whole list so it I have 15 rows on the list, and I filter so that only 5 are showing, cell a1=33%
How can I make a macro that will autofilter until the the a1=5% Like having filter criter = equal or greater then 1 hour, if a1 > 5% Then criteria + 1 hour If A1=<5%, then stop.
basically a seed criteria of 1 hour, adding 1 hour until the value in a1 = 5%
Been looking at various options. Just want something to simply show the autofilter critera (1 & 2 or how every many there are) in a text box. The range being filtered is column A : M.
Basically taking this further, want to use the criteria of column C to lookup an email address e.g.
'C' is filtered by "Company 96"
Lookup "Company 96" from sheet2 range A:B, with column A containing name, and column B containing email address
My problem is following: I have a list of data that are classified according to a particular character, and I want to copy (with auto filter through the macro) the relevant information to the appropriate place in the sheet where it belongs. That's no problem. The problem is that if I want to copy data, classified by a character that is not listed in the table (that is not in the filtering criteria), then all the data are copied to the appropriate place. But I do not want to copy in this case nothing. How should such a macro look like?
I have some data which I want to apply multiple criteria to for a particular column. Searched around on the internet and it would appear I should use an array and pass that to my criteria. What I can't find an answer for is how to say "does not equal any of the values within the array"
Code currently is:
Code: Dim NumberFilter_Array(0 to 2) NumberFilter_Array(0) = 2 NumberFilter_Array(1) = 9
[Code].....
I've tried variations for that in red but can't get it to work, how to correct the line in red to filter for none of the values in the array NumberFilter_Array?
I am trying to use VBA to set an autofilter that hides all zeros in Column AL and then excludes all values in Column E that start with "312" or "502". For some reason I can't get this to work as expected, it still continues to display unwanted values in Column E.
I have one master sheet of data, with a large number of fields and data.
I need to turn this master data into individual records, each record exisiting as an individual worksheet - lets call it a 'U'. The U is a template sheet which has calculations and lookups built into it to complete further information. The completed U's are then used by a number of people for different reasons. There are 3 main 'flavours' of these sheets which have slightly different uses.
I've gone from knowing nothing about macros to having learned enough about them in the last week or so to populate each individual sheet with the data, and save the new file in the location I want it to go.
What I want to do now is filter the fields displayed by the individual U sheets, as not every field is applicable to each 'flavour'. I've marked up the rows as to the appropriate flavour - e.g. Row 17 is applicable to 'P' 'F' and 'R' ( Cell which is auto filtered contains PFR), but Row 18 is only 'P' and 'F' (Cell contains PF).
I've gone through the master file and identified each entry as a the appropriate flavour - to summarise what I'd like to do now:
1) Automatically populate the template file with the relevant data. (which my macro will do) 2) Use an autofilter to filter the rows equal to the data in the reference sheet so these are the only ones displayed. Eg. Reference sheet says 'P', so I want to filter the U sheet where autofilter column contains the letter 'P' 3) Rename the file and save as my reference in the location I want it to (which the macro is doing).
(where CF3 = the cell in the master data with the flavour in it, "USS iss1" is the template U file, G158 is a spare cell and Autofilter Field 1 contains the row reference which tells me which data applies to which flavours).
I've tried using the macro recorder, which when I paste the value in the autofilter/contains box records it as the value I've just put in rather than a copy of the reference cell. I've tried
Criteria1:=*ActiveCell*
and other variations on the same theme, but to no avail - I get "Compile error: Expected:expression".
could u advise how to display just the criteria ie no heading, no :, no =, and when it displays the criteria could it fill the cell with a colour. When filter is set to "all" give a blank cell
This would over come lots complaints from operators not realizing that filters are on because they can not find the silly blue button.
Are microsoft aware of is and are they changing it in the new release.
I'm actually trying to write a Macro that applies autofilters and the information just doesn't show! The autofilter is properly applied and everything, but the only visible row is the header. The code is the following.
I would like to do some search function. As I know, to have "contains" in criteria, I can simply add *. But then, the asterisk(*) doesnt work with forms. I want my user to put the word they are looking for in a form then the system will select from a different worksheet. Below is my coding and bold is my biggest problem.
I want to write a VBA routine to compare the Autofilter criteria found on two different worksheets. If the criteria is not the same I will display an error message. The range the Autofilter is working on could be one to many columns. The criteria could be anything from selecting a value from the autofilter list to multiple columns using a custom autofilter with multiple conditions.
I'm trying to make the criteria in an autofilter bit of code be the value of a cell in my spreadhseet. I have named the cell and would like to reference the named range rather than the cell address if possible.
I have the following code based on Multiple user-selected dropdowns. What I need to add is I believe a Volatile Application, where if the user chooses the option "ALL" OR leaves blank (doesn't pick anything from the dropdown,) the respective column does not get Autofilter applied. I have 6 different criteria, including a Start Date and End Date. So, the final result I'm trying to get is to have autofilter applied to only those columns for which the user selected a specific option.
Dim dDate As Date Dim strDate As String Dim lDate As Long Dim rDate As Range
Worksheets("Data").AutoFilterMode = False Set rDate = Sheets("Summary").Range("B3")
If Not IsDate(rDate) Then 'Check if valid MsgBox "Non valid date and time" Exit Sub End If
I have 2 excel files, let's call them 1.xlsx and 2.xlsx (excel 2007)
File 1 is the file where I want data copied into and file 2 is the file I want to copy data from.
File 1 has certain text strings in every say 5th column in always row 2. I want to find those strings in file 2 and if the string is found, go 6 rows down, copy the cell, and paste it into file 1, 8 rows down the text string. this is the code I came up with, but it doesn't work
Code: Option Explicit Sub get_data_from_2()
Dim i As Long Dim j As Long Dim FinalColumn As Long Dim RngFrom As Range
And this criteria can be changed by the user anytime. Now for some reason sometimes I trigger a code which works under Function key(F8) and it refresh some data from the database. Just before it gets the data, it will remove the filter such as :
My problem is after I run my code (under F8), I want to filter back with the same criteria. Is there anyway that i record my criteria in any cell whenever I filter?
I have a Excel-spreadsheet (sheet1) which has dates in column G. I am looking for autofiltering data by year or month based on date value in column G. In my userform I have two comboboxes, one has years from 2009-2014 and another one are months from Jan-Dec. How can I write a code that shows only rows which match either year or month value based on combobox selection?
I populated a listbox with unique items from a list. I am trying to the get result of that listbox to goto Sheet2 and sort the table ( named: data_area) on that sheet. Code using below and attached file with example.
