[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Make VB Case Insensitive

Posted on 2009-12-18
4
Medium Priority
?
314 Views
Last Modified: 2012-05-08
I am trying to alter a macro to make it case insensitive.  It is used within an Excel template for a report one of our coders (medical, not computer) uses to post procedures from.  I just discovered today that the entity sending the file isn't always consistent in the procedure name (sometimes it's lower case or mixed).  How is this accomplished using the attached macro/code?  Thanks!!!
Option Explicit
Sub KillRows()
 
Dim Trange As Range
 Dim Row As Range
 
For Each Row In ActiveSheet.UsedRange.EntireRow.Rows
    If Row.Cells(1, "F") = "CT-CA++SCORE" Then
        If Trange Is Nothing Then
            Set Trange = Row
        Else
            Set Trange = Union(Trange, Row)
        End If
    End If
    Next Row
    
    If Not Trange Is Nothing Then
    Trange.Delete Shift:=xlUp
End If
 
End Sub

Open in new window

0
Comment
Question by:shaneleach
4 Comments
 
LVL 10

Accepted Solution

by:
Makrini earned 1000 total points
ID: 26081763
Try comparing the Uppercase value of the cell

(Added a "Ucase" below)
Option Explicit
Sub KillRows()
 
Dim Trange As Range
 Dim Row As Range
 
For Each Row In ActiveSheet.UsedRange.EntireRow.Rows
    If Ucase(Row.Cells(1, "F").Value) = "CT-CA++SCORE" Then
        If Trange Is Nothing Then
            Set Trange = Row
        Else
            Set Trange = Union(Trange, Row)
        End If
    End If
    Next Row
    
    If Not Trange Is Nothing Then
    Trange.Delete Shift:=xlUp
End If
 
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:shaneleach
ID: 31667817
Thank you Makrini!!!  I love EE!!!!  
0
 
LVL 1

Expert Comment

by:Altwies
ID: 26082106
Simply use the Format Function:

dim s1 as string

s1 = Format("This is a test", ">")

s1 will be all upper case

do this prior to testing your string data for all opperands and you will get case  insensitive compares.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 26083487
In VBA (not VB6) you can put

Option Compare Text

at the top of the code module(s) so that you don't have to convert each string to one particular case.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question