VBA code not case sensitive

KidlatKulug1

New Member
Joined
Nov 8, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
I found this code that i'm able to use but I need it to be not case sensitive, can someone please help?

VBA Code:
Dim rngCell As Range
Dim rngCellLoc As Range
Dim ws As Worksheet
Dim lngLstRow As Long
Dim lngLstCol As Long
Dim strSearch As String

Sheets("Master").Select
Range("B1:K6").ClearContents
lngLstRowLoc = Sheets("Master").UsedRange.Rows.Count
Application.ScreenUpdating = False
    For Each rngCellLoc In Range("A2:A" & lngLstRowLoc)
    i = 1
        For Each ws In Worksheets

            If ws.Name = "Master" Then GoTo SkipMe
                lngLstRow = ws.UsedRange.Rows.Count
                lngLstCol = ws.UsedRange.Columns.Count
                ws.Select
                    For Each rngCell In Range(Cells(2, 1), Cells(lngLstRow, lngLstCol))
                        If InStr(rngCell.Value, rngCellLoc) > 0 Then
                            If rngCellLoc.Offset(0, i).Value = "" Then
                                If ws.Name = "student cafe seating" Then
                                rngCellLoc.Offset(0, i).Value = ws.Name & " " & rngCell.Row - 1
                                Else
                                rngCellLoc.Offset(0, i).Value = ws.Name & " " & rngCell.Address
                                i = i + 1
                                End If
                            End If
                        End If
                    Next
SkipMe:
        Next ws
    Next
    Application.ScreenUpdating = True
    Worksheets("Master").Activate
    MsgBox "All done!"
End Sub
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
414
Office Version
  1. 365
Platform
  1. Windows
Hey, kind of late but using an option at module level helps too if none case sensitive string comparisons would be your desired default behavior.

Option Compare Text
 

KidlatKulug1

New Member
Joined
Nov 8, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
Hi Fluff,
I ran into an issue with this code. If I only want to search by last name, the search stop on the first one it found, it does no search the rest of student with same last name. Can you pls help?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
There is nothing in the code that makes it stop, once a match is found.
 

KidlatKulug1

New Member
Joined
Nov 8, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
There is nothing in the code that makes it stop, once a match is found.
it stop on my last worksheet "student cafe seating". entering "Student" to search should have given me all the student listed in the worksheet but it stop at the first one.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Maybe you need to increment the value of i for that sheet, which you don't currently do.
 
Solution
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,697
Messages
5,765,989
Members
425,321
Latest member
stefanov07

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
Top