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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Try:

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

Make sure the case of the function matches the case in parentheses.
 
Upvote 0
Which part are you talking about?
 
Upvote 0
This makes Instr not case sensitive

VBA Code:
If InStr(1, rngCell.Value, rngCellLoc, vbTextCompare) Then  ....
 
Upvote 0
In that case the suggestion from JEC, should work.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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
Back
Top