TheSafehouse



I know not with what weapons World War III will be fought, but World War IV will be fought with sticks and stones. - Albert Einstein




Safehouse Forums



Safehouse Site Info



 

The end is nigh


 

 

 







FAQ Members List Calendar Mark Forums Read

Reply
 
Thread Tools
Pearll
Pearll is offline
Veteran

Join Date: Apr 2002
Location: Anchorage, Alaska


Pearll's Avatar

 
VBA in Excel help
Reply With Quote   #1

I need some help with some VBA coding in Excel. What I'm trying to do is compare values between two sheets in a workbook.

I have the following columns:

Sheet1, column A: Employee Names

Sheet2, column A: Employee Names
Sheet2, column B: Employee Numbers


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.


braincandy99
braincandy99 is offline
Veteran

Join Date: Aug 2001
Location: The Netherlands


braincandy99's Avatar

 
Re: VBA in Excel help
Reply With Quote   #2

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.

cheers


hoppabd
hoppabd is offline
Registered Member

Join Date: Mar 2006


 
Re: VBA in Excel help
Reply With Quote   #3

I always get these two screwed up - HLOOKUP and VLOOKUP. One of these two functions will do what you want.



What ever doesn't make you stronger, kills you.
braincandy99
braincandy99 is offline
Veteran

Join Date: Aug 2001
Location: The Netherlands


braincandy99's Avatar

 
Re: VBA in Excel help
Reply With Quote   #4

Here's an example.

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
cheers


Tivia
Tivia is offline
Weird Store
Snack Pervert

Join Date: Dec 2003


Tivia's Avatar

 
Re: VBA in Excel help
Reply With Quote   #5

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.




"Check out my youtube channel. Featuring League of Legends, FFXIV:ARR, gameplay and commentary."
Pearll
Pearll is offline
Veteran

Join Date: Apr 2002
Location: Anchorage, Alaska


Pearll's Avatar

 
Re: VBA in Excel help
Reply With Quote   #6

braincandy99 said:
Here's an example.
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?


Dragynphyre
Dragynphyre is offline
Veteran

Join Date: Mar 2000
Location: The Land of the Ice and Snow


Dragynphyre's Avatar

 
Re: VBA in Excel help
Reply With Quote   #7

I don't know VBA, I'd do it as a vlookup.



Pearll
Pearll is offline
Veteran

Join Date: Apr 2002
Location: Anchorage, Alaska


Pearll's Avatar

 
Re: VBA in Excel help
Reply With Quote   #8

Dragynphyre said:
I don't know VBA, I'd do it as a vlookup.
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.


braincandy99
braincandy99 is offline
Veteran

Join Date: Aug 2001
Location: The Netherlands


braincandy99's Avatar

 
Re: VBA in Excel help
Reply With Quote   #9

in vba, declarations are not required. undeclared variables become variants.

in my code above you would declare the s as string since you know it will be and treat it as a string.

you would then, more safely, force the contents of the cell to be a string with something like s = str(sheet1.cells(1,1)).

cheers


Pearll
Pearll is offline
Veteran

Join Date: Apr 2002
Location: Anchorage, Alaska


Pearll's Avatar

 
Re: VBA in Excel help
Reply With Quote   #10

Thank you! I got that all figured out using your code as a guide.

I'm using the following code to make a copy of a worksheet. Is there a way to copy values instead of formulas?

Code:
Sheets("Filtered").Copy Before:=Sheets(1)


braincandy99
braincandy99 is offline
Veteran

Join Date: Aug 2001
Location: The Netherlands


braincandy99's Avatar

 
Re: VBA in Excel help
Reply With Quote   #11

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.


Pearll
Pearll is offline
Veteran

Join Date: Apr 2002
Location: Anchorage, Alaska


Pearll's Avatar

 
Re: VBA in Excel help
Reply With Quote   #12

braincandy99 said:
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.




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump







 
All times are GMT -7. The time now is 05:37 PM.
All posts are
© Copyright 2004-2006 The Safehouse Network