vba code in excel

rajpeter · #1 09-06-2011, 05:11 AM

hi
i need your help i have in sheet column 2
like Peter, RAJ

but i am comparing with other excel sheet 1 like RAJ,PETER
i am comparing sheet 2 against sheet1. here i am showing part of the code
how do i write

Code:

If Not IsEmpty(FullName) Then
intFullNameNum = InStr(1, FullName, ",")
If intEmpNameNum >= 1 Then 'Real Person's Name strEmpLNameChrUpper = UCase(Mid(EmpFullName, 1, 1))
strEmpFNameChrUpper = UCase(Mid(EmpFullName, intEmpNameNum + 1, 1))
strEmpLName = Trim(strEmpLNameChrUpper & LCase(Mid(strEmpFullName, 2, intEmpNameNum - 2)))
strEmpFName = Trim(strEmpFNameChrUpper & LCase(Mid(strEmpFullName, intEmpNameNum + 2)))
strEmployeeBothName = strEmpLName & ", " & strEmpFName
 
 
the above code shows PETER,RAJ
BUT IN SHEET 2 shows PETER, RAJ
 
Set M = Worksheets("Sheet2").Columns(2).Find(What:=x, MatchCase:=True)

THNAKS

Catalin.B · #2 09-06-2011, 09:20 AM

Quote:

the above code shows PETER,RAJ
BUT IN SHEET 2 shows PETER, RAJ

Code:

strEmployeeBothName = strEmpLName & ", "& strEmpFName

sorry, but the above code shows PETER, RAJ not PETER,RAJ as you say (look at the line 7 in your posted code)
at this row in your code, it says : put between the last name and first name a comma and a space... if you don't want a space between names, just delete the space after the comma...
and you can change in your code the lower case to upper case to match the format in the other sheet...
How do you want to use the results of this comparison?
(maybe if you upload a sample with data and desired results, will ease communication...)

rajpeter · #3 09-06-2011, 09:44 AM

HI
thanks for your reply i have sample sheet. different names last name and first name
some of them have - or . in middle
and first name
but all of them last name and first name are samein sheet 1 and sheet2.
but i need to have the same columns in each sheet if it has the same last name and first name.

Catalin.B · #4 09-06-2011, 10:39 PM

it's not an easy task, because there are too many variables in your names...
to be specific, first name in column A, sheet 1, PETER, RAJ, in sheet 2 is PETER, RAJ(DEV)
second entry sheet1: SUBBU PERIAY, RAM, in sheet 2: SUBBU-PERIAY, RAM
third entry: SEKAR, RAM in sheet 2: SEKAR, RAM(there are 2 spaces between names in sheet 2! )
If you say there could be an "." between names, all of this will mess up any code
only formulas that searches text within cell (not range...) will have some results (like : SEARCH("Peter";A1;1) or =IFERROR(IF(AND(SEARCH("Peter";A1;1);SEARCH("Raj"; A1;1))>0;1;0);0)
for this name, for example, PETER, RAJ(DEV) needs a formula to remove "(DEV)" from right name
My advice is , if possible, to assign an unique ID for people in your list...
searching after an ID is A LOT easier

rajpeter · #5 09-11-2011, 06:11 PM

thanks
but where can i use this code . still confusing. please help me
thanks

rajpeter · #6 09-11-2011, 06:13 PM

when we have more than 1000 names how do we use unique id. compareing the names last name and first name

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA Code in Project to Wake Up/Open Excel	OTPM	Excel Programming	2	05-23-2011 02:24 PM
Any tutorial same as to this excel sheet with code?	Learner7	Excel	1	05-18-2010 05:31 PM
VB Code in Excel Active worksheet	shakilhyd	Excel	2	05-17-2010 07:50 AM
Excel ->VB code for Checkbox (control toolbox)	kirkstyle	Excel	0	08-16-2006 04:17 PM
Delete excel files by code in Access	Barry Richardson	Office	0	06-13-2005 06:26 AM