Search lastname + firstname (search on uppercase)
 

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

Search lastname + firstname (search on uppercase)
 

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.

--
Regards,
Tom Ogilvy


"Fr. Vandecan" wrote:

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

Search lastname + firstname (search on uppercase)
 

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