Like Function without Case Sensitivity for String Searches

[J_Raab]

Hi All,

I have a file with a LOT of data, particularly names, so I wrote a quick search macro that opens and closes the columns based on the entry from the user so they don't have to scroll through thousands of columns. It works wonderfully, and is actually pretty quick for the amount of information it goes through, but I'm running into a problem where the case sensitivity causes a "bug" of sorts. If someone enters "Jo" into the search cell, the macro runs properly and it shows only columns with "Jo" in them (i.e. John, Johnathan, Jordan etc...) and collapses the ones that don't match. The problem is if the user types "jo" none of those come up, since it seems to be case sensitive. I've put my code below, if anybody can assist with how to remove the case sensitivity I'd appreciate it, I'm sure its probably something simple, I'm just not finding it through my normal forum searches. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("B6")) Is Nothing Then


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.StatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False


Dim myRange As Range, myCell As Range, searchTerm As String
Set myRange = Range("I1:UD1")
searchTerm = Range("B6")
    
    For Each myCell In myRange
        If myCell Like "*" & searchTerm & "*" Or myCell.Value = "" Then
            myCell.EntireColumn.Hidden = False
        Else
            myCell.EntireColumn.Hidden = True
        End If
    Next myCell


End If


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.StatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True

 

[Fluff]

try

searchterm = LCase(Range("B6"))
    
    For Each MyCell In myRange
        If LCase(MyCell.Value) Like "*" & searchterm & "*" Or MyCell.Value = "" Then
            MyCell.EntireColumn.Hidden = False
        Else
            MyCell.EntireColumn.Hidden = True
        End If
    Next MyCell


End If

 

[J_Raab]

Have you ever asked a question......heard/read the response and just kind of gone...... "Really....."...........yeah, that's me right now. Thanks Fluff, much love.

 

[Fluff]

You're welcome & thanks for the feedback