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:

KidlatKulug1

New Member
Joined
Nov 8, 2021
Messages
18
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.
In the worksheet "Master" cell A2 (and A3 if more than one entry) the student(s) name will be input and the search (via macro) that student(s) name from multiple worksheets. If found it will have output display in worksheet "Master" in cell B2, C2, D2 and so on...of the name of worksheet and the column/row the student(s) name was found (example below). The issue I'm running into is case sensitivity. Note the first entry Student 50 (with capital letter S) while next row student 50 (s is in small letter) which I'm expecting to have same output.
Please Enter First &/or Last Name of Students below (Note: case sensitive)
Student 50White $F$3student cafe seating 10
student 50

Please Enter First &/or Last Name of Students below (Note: case sensitive)
student 50
Student 50White $F$3student cafe seating 10
Student 91Blue $F$6student cafe seating 30
student 91
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Will the student names always be in col A of each sheet?
 

KidlatKulug1

New Member
Joined
Nov 8, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Will the student names always be in col A of each sheet?
No, Names of students are in different columns of each worksheet, all I care about is that search should not be case sensitive, regardless where/which column on multiple worksheet.
below is a fictitious sample of the worksheets (Blue and student cafe seating).
Route: 3AColumn 1Column 2Column 3Empty columnColumn 4Column 5
Group 1Student 22Student 7Student 9
Group 2Student 17Student 25
Group 3Student 8Student 18Student 52Student 29
Group 4Student 40
Group 5Student 90Student 91


Seat #T1 11:30-11:50T2 12:20-12:40T3 11:55-12:15T4 12:30-12: 50
Row 1 -1 LStudent 3Student 60Student 6
Row 1 - 2 CStudent 1Student 18
Row 1 -3 RStudent 61Student 4
Row 1 -4 LStudent 62Student 2
Row 1 -5 CStudent 20Student 5Student 45
Row 1 -6 RStudent 21Student 7Student 46
Row 1 -7 LStudent 8Student 22Student 47
Row 1 -8 CStudent 9Student 23Student 48
Row 1 -9 RStudent 10Student 24Student 49
Row 1 -10 LStudent 11Student 25Student 50
Row 1 -11 CStudent 12Student 51
Row 1 -12 RStudent 13Student 52
Row 1 -13 LStudent 14Student 53
Row 1 -14 CStudent 15
Row 1 -15 RStudent 16Student 37
Row 1 -16 LStudent 17Student 38
Row 1 -17 CStudent 26Student 39
Row 1 -18 RStudent 27Student 40
Row 2 - 19 LStudent 28Student 41
Row 2 - 20 CStudent 29
Row 2 - 21 RStudent 30
Row 2 - 22 LStudent 31
Row 2 - 23 CStudent 32
Row 2 - 24 RStudent 33
Row 2 - 25 LStudent 34
Row 2 - 26 CStudent 35
Row 2 - 27 RStudent 36
Row 2 - 28 LStudent 89
Row 2 - 29 CStudent 90
Row 2 - 30 RStudent 91
Row 2 - 31 L
Row 2 - 32 C
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Ok, the mod that JEC suggested should work. Can you re-post your code using that mod, which you say doesn't work?
 

KidlatKulug1

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

ADVERTISEMENT

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

ADVERTISEMENT

That code does not use the the mod that JEC suggested.
 

KidlatKulug1

New Member
Joined
Nov 8, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
That code does not use the the mod that JEC suggested.
crap, sorry i copied the wrong file
but here is the body of the code
For Each rngCell In Range(Cells(2, 1), Cells(lngLstRow, lngLstCol))
If InStr(rngCell.Value, rngCellLoc) > 0 Then
If InStr(1, rngCell.Value, rngCellLoc, vbTextCompare) 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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
It should replace this
VBA Code:
InStr(rngCell.Value, rngCellLoc)
so you get
VBA Code:
                    For Each rngCell In Range(Cells(2, 1), Cells(lngLstRow, lngLstCol))
                        If InStr(1, rngCell.Value, rngCellLoc, vbTextCompare) > 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
Also when posting code please use code tags How to Post Your VBA Code
 

KidlatKulug1

New Member
Joined
Nov 8, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
It should replace this
VBA Code:
InStr(rngCell.Value, rngCellLoc)
so you get
VBA Code:
                    For Each rngCell In Range(Cells(2, 1), Cells(lngLstRow, lngLstCol))
                        If InStr(1, rngCell.Value, rngCellLoc, vbTextCompare) > 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
Also when posting code please use code tags How to Post Your VBA Code
that works, thank you very much.
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,699
Messages
5,766,001
Members
425,322
Latest member
galaxy6623top

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