Like Function without Case Sensitivity for String Searches

J_Raab

New Member
Joined
Apr 7, 2017
Messages
16
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.

Code:
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
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,992
Office Version
365
Platform
Windows
try
Code:
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

New Member
Joined
Apr 7, 2017
Messages
16
Have you ever asked a question......heard/read the response and just kind of gone...... "Really.....:banghead:"...........yeah, that's me right now. Thanks Fluff, much love.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,992
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,081,563
Messages
5,359,618
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top