Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
separate lastname from firstname | Excel Worksheet Functions | |||
Is there a way to separate FirstName and LastName ? | Excel Discussion (Misc queries) | |||
LastName, FirstName covert to FirstName LastName | Excel Discussion (Misc queries) | |||
How to change lastname, firstname to firstname lastname in Excel? | Excel Discussion (Misc queries) | |||
LastName, FirstName format | Excel Programming |