VBA code not case sensitive

[KidlatKulug1]

I found this code that i'm able to use but I need it to be not case sensitive, can someone please help?

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

 

[KidlatKulug1]

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 50	White $F$3	student cafe seating 10
student 50

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

 

[Fluff]

Will the student names always be in col A of each sheet?

 

[KidlatKulug1]

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: 3A	Column 1	Column 2	Column 3	Empty column	Column 4	Column 5
Group 1	Student 22		Student 7			Student 9
Group 2		Student 17			Student 25
Group 3	Student 8	Student 18	Student 52			Student 29
Group 4					Student 40
Group 5			Student 90		Student 91

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

 

[Fluff]

Ok, the mod that JEC suggested should work. Can you re-post your code using that mod, which you say doesn't work?

 

[KidlatKulug1]

ADVERTISEMENT

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

 

[KidlatKulug1]

Ok, the mod that JEC suggested should work. Can you re-post your code using that mod, which you say doesn't work?

ops, sorry posted but not in correct manner

 

[Fluff]

ADVERTISEMENT

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

 

[KidlatKulug1]

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]

It should replace this

InStr(rngCell.Value, rngCellLoc)

so you get

                    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]

It should replace this

InStr(rngCell.Value, rngCellLoc)

so you get

                    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.