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:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,825
Try:

VBA Code:
If LCase(ws.Name) = "master" Then GoTo SkipMe

Make sure the case of the function matches the case in parentheses.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Which part are you talking about?
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
797
Office Version
  1. 365
Platform
  1. Windows
This makes Instr not case sensitive

VBA Code:
If InStr(1, rngCell.Value, rngCellLoc, vbTextCompare) Then  ....
 

KidlatKulug1

New Member
Joined
Nov 8, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This makes Instr not case sensitive

VBA Code:
If InStr(1, rngCell.Value, rngCellLoc, vbTextCompare) Then  ....
The input text in cell A2 needs to be no case sensitive
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
In that case the suggestion from JEC, should work.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
In what way?
 

KidlatKulug1

New Member
Joined
Nov 8, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
In what way?
When the input in cell A2 is "first student" instead of "First Student", there is no error and there are no output like the output I get from the input "First Student"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Which cell A, your code is looking at multiple sheets?
You would be far better of actually explaining what you are trying to do & post some sample data from your sheets.
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,698
Messages
5,765,991
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