Search lastname + firstname (search on uppercase)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have 2 columns a and b containing firstname (a), lastname (b). The data is
input like allways (you never know how) sometimes upper case and lower case
are mixed etc ...
So I would like to be able to search on upper case for both columns. How do
i combine search on both columns and upper case ?

Thanks for help
 
turn on the macro recorder. Select both columns, Do Edit=>find

Uncheck the match case check box.

after completing the find, turn off the macro recorder. Adapt the code.
 
It's not clear how you want "upper case" to apply. The following will
find SMITH & JOHN, but not Smith & John. Using the 2nd If stmt
(commented out) would find Smith & John. Using the 3rd If stmt
(commented out) would find only SMITH & JOHN.

Hth,
Merjet


Sub FindName()
Dim bFound As Boolean
Dim iEnd As Integer
Dim c As Range
Dim rng As Range

iEnd = Sheets("Sheet1").Range("A1").End(xlDown).Row
Set rng = Sheets("Sheet1").Range("A1:A" & iEnd)
For Each c In rng
Debug.Print c
If c = "SMITH" And c.Offset(0, 1) = "JOHN" Then
'If UCase(c) = "SMITH" And UCase(c.Offset(0, 1)) = "JOHN" Then
'If c = UCase("Smith") And c.Offset(0, 1) = UCase("John") Then
MsgBox "Found on row " & c.Row
bFound = True
Exit For
End If
Next c
If bFound = False Then MsgBox ("Name not found.")
End Sub
 
Back
Top