The code needs to read the list of Employee Names in Column A of Sheet1, search for matching names in Column A of Sheet2, and replace the Employee Name in Column A of Sheet1 with the corresponding Employee Number in Column B of Sheet2.
To give an example:
John Smith is in A2 of Sheet1.
John Smith is also in A36 of Sheet2.
His employee number (123456) is locate adjacent to his name in Sheet2 (B36)
The value of A2 on Sheet1 is changed to that of his employee number, making it 123456.
I don't have Excel in front of me but you basically need to do the following:
A nested for/next loop if the number of items in the column doesn't change and is known. A nested Do/While loop otherwise.
You want to use the strcomp type of command. There are a few different ones. A safe one is:
if instr(1, Sheet1.cells(A2,x), Sheet2.cells(A2,y)) > 0 then
'This means the match is found
Sheet1.cells(A2,x) = Sheet2.cells(A3,y)
else
endif
basically what the command above does is check 'in the string (instr)' starting at the specified character (1, start of string), of the specified string (Sheet1.cells(A2,x), where x is one of the loop variables) if the specified string (Sheet2.cells(A2,y), where y is the other loop variable) is present.
Note I use 'A2' but you would have to put the actual column number.
I can give more specifics tomorrow maybe, but that should get you close enough. There are other string comparison functions.
I put the sheet1 names in column 1, the sheet2 names in column1 and the sheet2 numbers in column 2.
Code:
Public Sub rearrange()
s = Sheet1.Cells(1, 1)
i = 1
j = 1
Do While s <> ""
For j = 1 To 100 'arbitrary end point of your choice
If Sheet2.Cells(j, 1) = "" Then Exit For 'exit if no name exists on sheet2
If InStr(1, Sheet1.Cells(i, 1), Sheet2.Cells(j, 1)) > 0 Then
Sheet1.Cells(i, 2) = Sheet2.Cells(j, 2) 'i put the numbers in a different column for safety/checking
Exit For
End If
Next
i = i + 1
s = Sheet1.Cells(i, 1)
Loop
End Sub
I will confirm Braincandy's code. I saw this the other night but I was exhausted when I saw it. I know better than to write excel vba code when I am in that state of mind lol.
Been a really long two weeks..phone company installed voip phones to replace the old analog while I was in the hospital. They managed to send an idiot who essentially crashed my network and then for unknown reasons deleted the dhcp tables because he couldn't figure out how to vlan tag his phones into the network. So although I had backups, I still had to learn how to configure their server and essentially reinstall the entire bloody phone system. Never mind the fact that I have never installed a phone system in my life as I am a server/network guy, not a phone guy lol. Anyhow, all done now but yeesh I likely worked 160+ hours in the past two weeks. Surprised I didn't put myself right back in the hospital.
"Forums: You have to form your opinions immediately based on jack s*it then fight them to the death! It's like Highlander, but instead of swords we use ignorance."
I'm somewhat of a newb when it comes to VBA coding and have been learning along the way in order to complete this project. What sort of variable do I need to declare "s" as in your code?
I'm trying to do it all in VBA so that I can automate quite a bit of formatting that's needed after pasting data into it from another report. I've been able to delete columns, condense and total data for employees with multiple listings, remove duplicate listings, add and populate additional columns, etc. It's just this one part that has me stumped.
you might play with the macro record function and copy and paste (using paste special...values, instead of the normal paste).
I rarely combine VBA work and formulas in cells (unless I am using vba to put the formula in the cell)...
edit: I don't think you can paste an entire sheet using the paste special, so you would have to do it in two steps: 1. create a new sheet. 2. select all cells of the sheet you want to copy. 3. paste special into the new sheet.
edit: I don't think you can paste an entire sheet using the paste special, so you would have to do it in two steps: 1. create a new sheet. 2. select all cells of the sheet you want to copy. 3. paste special into the new sheet.
That's what I was afraid of. I was hoping there was a cleaner way to do it than the recorded macro, but that will have to do.
