Resolved this issue via a pivot table but would like to know if there is a flaw or issue with Excel that prevents you from using the "<" or ">" characters as a reference or part of a reference in a function.
Kurupt,
Maybe I wasn't clear at first.
The "<" & ">" characters are part of the heading used in the criteria of the CountIf function.
The CountIf function is referencing the following formula:
=IF(C13<8,"<8",IF(AND(C13>7,C13<16),"8-15",IF(AND(C13>15,C13<31),"16-30",IF(C13>30,">30"))))
Example...
All.
I have a table using the CountIf function in Sheet1 to capture data from Sheet2.
The table is setup as:
Fails By Age
<8 (col.K) 8-15 (col.L) 16-30 (col.M) >30 (col.N)
The CountIf function works for cols. L & M but not for K & N.
Is this because the criteria contains...
Hmmm...seems strange.
Why not just create a new List Box ?
Based on your example I tried it and it worked fine.
Have you checked the range of your list ?
All.
Presently I recorded the Find/Replace function to look for any data that had "19" & to replce it with "20".
Upon further review of this data (in mm/dd/yyyy format)I would like to know if there is a way to code Find/Replace so that it only looks for the first two digits of the yyyy area ...
Yes.
I get the following Run-time error '1004': "Unable to get the Specialcells property of the range class"
Code:
Sub Macro5()
'
[A5].AutoFilter
[A5].AutoFilter Field:=3, Criteria1:="=SP*"
With [A5].CurrentRegion
.Range(.Cells(2, 1), .Cells(.Rows.Count...
Skip,
The result of the custom filter gives Row 56 the starting row with data that starts with "SP".
I start at row 5 because that is where my header is.
If my starting row for deletion changes (which it will) how do I adjust my code ?
Thanx for your help.
All.
Here is the posted code using the custom filter feature.
My question is how do I adjust it to capture all rows once the filter is on when the first row changes daily ?
From the code, today's data starts at row 56.
Code:
Rows("5:5").Select
Selection.AutoFilter
Selection.AutoFilter...
Skip,
1. My original post did not state what my intention: I need the macro to take any data starting with "SP" in column "C" and deleting the row associated with it.
2. I did take out the End If & yes, the code you supplied does delete that row only.
Gavin,
I agree the Autofilter is easier...
EB,
I had tried that but it did not work.
All the code does is highlight col.C.
While that is fine, I then need the macro to take any data starting with "SP" and deleting the row associated with it.
Skip,
Thanks for the guidance but now I get a Compile Error: EndIF without block If
Code:
Sub macro2()
'
Columns("C:C").Select
Range(Selection, Selection.End(xlDown)).Select
If Left([c6], 2) = "SP" Then [c6].EntireRow.Delete
End If
End Sub
Please advise.
All.
In col.D I have some data that is identified as SP3 & SP 50.
How can I incorpoarate the follwing logic into code ?
=if(left(D1,2)=SP,then delete entire row.
Thanx for any help & guidance.
All.
I have a set of data in date format (mm/dd/yyyy).
I need the macro to find "19" & replace it with "20". Simple enough except "19" shows up in the dd section.
In short, I need to change all of the data in years only from 19 to 20 while leaving the day portion alone.
Control/Find changes...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.