different the Capital letter in 1st 5 character using VBA excel 2003

A

Alexandro88

New Member
Joined
Nov 11, 2010
Messages
35
Hi ,

This is my Table in sheet excel 2003, i use filter in column IdNum and Channel:
<table width="305" border="0" cellpadding="0" cellspacing="0"><col style="width: 137pt;" width="183"> <col style="width: 92pt;" width="122"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" id="_x0000_s1025" style="height: 12.75pt; width: 137pt;" width="183" height="17">IdNum</td> <td class="xl24" id="_x0000_s1026" style="width: 92pt;" width="122">Channel</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">214U-Press Web</td> <td>Pass</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">234U-Multimedia</td> <td>Fail</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">2345-Games</td> <td>Fail</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">45U-University</td> <td>Fail</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">455-School</td> <td>Pass</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">611U-Multi</td> <td>Pass</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">554-primary</td> <td>Fail</td> </tr> </tbody></table>
Click to expand...
Then i write a script in VBA excel 2003 to list Channel= Fail. This is my code:
Code: 
Sub Column()
Worksheets("Case Details").Range("R4").AutoFilter _
  Field:=18, _
  Criteria1:="Failed", _
  VisibleDropDown:=True
End Sub
The output is like this:
<table width="305" border="0" cellpadding="0" cellspacing="0"><col style="width: 137pt;" width="183"> <col style="width: 92pt;" width="122"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" id="_x0000_s1025" style="height: 12.75pt; width: 137pt;" width="183" height="17">IdNum</td> <td class="xl24" id="_x0000_s1026" style="width: 92pt;" width="122">Channel</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">234U-Multimedia</td> <td>Fail</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">2345-Games</td> <td>Fail</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">45U-University</td> <td>Fail</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">554-primary</td> <td>Fail</td> </tr> </tbody></table>
Click to expand...
Then i want to different IdNum that contain Capital letter " U" in 1st 5 character .The output i want is like this:
<table width="305" border="0" cellpadding="0" cellspacing="0"><col style="width: 137pt;" width="183"> <col style="width: 92pt;" width="122"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" id="_x0000_s1025" style="height: 12.75pt; width: 137pt;" width="183" height="17">IdNum</td> <td class="xl24" id="_x0000_s1026" style="width: 92pt;" width="122">Channel</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">234U-Multimedia</td> <td>Fail</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">45U-University</td> <td>Fail</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> </tr><tr><td valign="top">
</td><td valign="top">
</td></tr><tr><td valign="top">
</td><td valign="top">
</td></tr><tr><td valign="top">
</td><td valign="top">
</td></tr> </tbody></table>
Click to expand...
How to different it ? I need to write in VBA excel 2003 code. Any 1 can help me?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Code: 
With Worksheets("Case Details")
    .Columns("S").Insert
    .Range("S4").Value = "tmp"
    .Range("S5").Resize(.Range("R4").End(xlDown).Row - 4).Formula = "=AND(R5=""Fail"",ISNUMBER(SEARCH(""U-"",Q5)))"
    .Range("S4").AutoFilter _
        Field:=19, _
        Criteria1:="TRUE", _
        VisibleDropDown:=True
End With
 
Upvote 0
Another way without the helper column

Code: 
With Worksheets("Case Details")
    .Range("Q4").AutoFilter _
        Field:=17, _
        Criteria1:="=*U-*", _
        VisibleDropDown:=True
    .Range("$Q$4:$R$11").AutoFilter _
        Field:=18, _
        Criteria1:="Fail"
End With
 
Upvote 0
One slight snag Bob,
Code: 
Criteria1:="=*U-*", _
is not case sensetive, it would filter "U" and "u".

That was a method I tried but abandoned, been working with the advanced filter solution

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 80px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>U</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Exact Match</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>U2</TD><TD>=ISNUMBER(FIND("U",LEFT(Q5,5)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

As criteria range, just trying to figure if there is a way to code it without using the 2 cells on the sheet to hold the criteria.
 
Upvote 0
Hi Bob Phillips,

If my sheet is like this :
<table width="305" border="0" cellpadding="0" cellspacing="0"><col style="width: 137pt;" width="183"> <col style="width: 92pt;" width="122"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 137pt;" width="183" height="17">IdNum</td> <td class="xl24" style="width: 92pt;" width="122">Channel</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">234U-Multimedia</td> <td>Fail</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">45U: University</td> <td>Fail</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">35U House</td> <td>Fail</td> </tr> </tbody></table>
Click to expand...

This code is not suitable:
Code: 
Criteria1:="=*U-*", _
</pre>

How?
 
Upvote 0
That code is looking for the "-" after "U", hence the reason for the fail.

As far as I can see you have 2 possible solutions, the first is to hide rows based on criteria, not to filter them. The second is to use advanced filter, even with the filter being called from VBA you will still need cells on the worksheet to hold the criteria.
 
Upvote 0
Add a helper column, formula =ISNUMBER(FIND("U",LEFT(Q4,5)))

Then filter on that, criteria = "TRUE"
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
VBA for Web Scraping help
Replies
0
Views
624
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
M
  • Question
Drop down select
Replies
1
Views
1K
madu
M
A
  • Question
Web Scraping using VBA
Replies
4
Views
9K
atr140
A
B
  • Question
Index and match vertical and horizontal data
Replies
6
Views
1K
DanteAmor
DanteAmor

Forum statistics

Threads
1,216,124
Messages
6,128,993
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top