How to address case sensitivity in vba code

Question

I have a code which searches a column of text in excel for a student names.

The code I am using is:

Private Sub CommandButton1_Click()
row_number = 0
count_of_string = 0
search_string = Sheets("Names").Range("C3")
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("Names").Range("A" & row_number)
If InStr(item_in_review, search_string) > 0 Then
    count_of_string = count_of_string + 1
End If
Loop Until item_in_review = ""
MsgBox (search_string & " occurred: " & count_of_string & " times.")
End Sub

This works fine. The problem I am having is that if the user changes the case of any name while typing it in the search field, the code does not 'see' the name. For instance, if the user searches for "mark" instead of "Mark" as found in the column, the code incorrectly says that it does not exist. How can I re-write this, please?

possible duplicate of How to modify this script to account for different case? — RubberDuck
– RubberDuck, Commented May 18, 2015 at 13:26

Dawid · Accepted Answer · 2015-05-18 13:09:21Z

3

You can use UCase Function -> http://www.techonthenet.com/excel/formulas/ucase.php

try this:

If InStr(UCase(item_in_review), UCase(search_string)) > 0 Then
    count_of_string = count_of_string + 1
End If

answered May 18, 2015 at 13:09

Dawid

7863 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Iki Over a year ago

Thank you. That solves my problem. I have accepted this as my answer.

Collectives™ on Stack Overflow

How to address case sensitivity in vba code

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related