VB Macro in Excel Problem

  • 18 years ago

    It's been a while since I've done any VB, and I've never really used it in macros in Excel, so I need a touch of help.


    I've got a spreadsheet with 6 worksheets in it named;
    MasterList
    MAIL1
    MAIL2
    MAIL3
    MAIL4
    MAIL5


    The Master List contains around 11000 First Names, Last Names, and User Names.


    The other 5 sheets contain ONLY usernames, sorted out by servers (mail1-5)


    I need to populate the MAIL1-5 worksheets with first/last names grabbed from the Masterlist sheet. I'm assuming this should be pretty easy using a quick VB loop, but I don't know how to execute it. Any help?




    Additional Info:
    For example, if Masterlist had the following entry:
    Joe        Bob       bobj


    and MAIL1 had the following entry:
    bobj


    I want the MAIL1 entry to be populated like so:
    bobj     Bob      Joe

  • 18 years ago

    All you need is the VLookup cell function.  Say column A has first name, col B has last name, and C username.  You need to set Username as column A.  To do this, just cut the entire column and insert it as column A (copy/paste).


    Now, to get the First Name, use this (assuming First name is now col B):


    =vlookup(A2,MasterList!$A$2:$C$11000,2,false)



    And for last name, use this:


    =vlookup(A2,MasterList!$A$2:$C$11000,3,false)



    Note that the 11000 is the last row containing info on MasterList, and A2 is supposed to be the start of it, and, in mail1 through 5, I assume the username you want to search for is contained in column A.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Measuring programming progress by lines of code is like measuring aircraft building progress by weight.” - Bill Gates