Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use vloopup in VBA macro (2003)

    I am trying to write a macro in VB which compares a number from one file (orig file) with another then when that number is found it looks for the engineer name which it turn would relate to a team name, this team name is then placed in the field team.
    See screenshot for details.
    Attached Images Attached Images

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use vloopup in VBA macro (2003)

    It needn't be done in VBA as you can use the following syntax (assume look up value is in A2 of the current worksheet)

    =VLOOKUP(A2,[ExternalWorkbook.xls]WorksheetName!$A$2:$B$11, 3.0)


    OR

    If if a path is required

    =VLOOKUP(A2,'Cocuments and Settingsjj1972Desktop[ExternalWorkbook.xls]WorksheetName'!$A$2:$B$11, 1,3,0)

    Obviously adjust the names and paths as required but thi sshould indicate what is required
    Jerry

  3. #3
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use vloopup in VBA macro (2003)

    Can you send me an example please.
    i.f name mason then team is IDND goes in column "Team".
    and say number 123456 is in both spreadsheets.
    how would l know which person correspondances to which team?

  4. #4
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use vloopup in VBA macro (2003)

    Also, how could l incorporate into a macro to automate this task?

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use vloopup in VBA macro (2003)

    You will have to wait I 'm afraid as I am at work at the moment.

    Putting my teachers hat on here....... We are not here to do the work for you, we are here to guide you. You are always asking us to do the work for you, could you do me a favour and send a cut down version of the two excel workbooks so that I can work with data that is relevant to you please?

    Have you actually tried this yourself yet? To answer the question above, if you have this as a formula you do not need a macro.
    Jerry

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use vloopup in VBA macro (2003)

    OK , here are the two files in question. As you have not provided me with any sample data mimicking your requirement I am working in the dark.

    Place the two files on your root directory and open jj1972.xls. There is no need to have source.xls open to get this to work but you may want to review it to see how the table is set. Have a look at column D in jj1972.xls and you will see the formula that is used. This is assuming that the two workbooks are in the same folder. If they are different you will have to use the syntax of the second formula I provided.
    Jerry

  7. #7
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use vloopup in VBA macro (2003)

    Hello,

    That is nearly what l require but not quite. I should have provided you with more info.
    The source file contains the data that l need to be extracted from which contains the name of the enginner.
    The original file contains the heading Team. If the engineer is of a certain name then there team name will either be IDND or IISS which will go in the original file in the column team.
    How and where will l define which engineer relates to which team?

    See attachments for details.
    Attached Files Attached Files

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use vloopup in VBA macro (2003)

    In your Source XL File create a table ( Say in L3:M4 for this example)

    <table border=1><td>Leif</td><td>IDND</td><td>Hans</td><td>IISS</td><td>Charlotte</td><td>IDND</td></table>

    In cell C2 of your Source type the following :

    =VLOOKUP(B2,$L$2:$M$4,2,FALSE)

    Now go to your Original XL file and replace the VLOOKUP with:

    =VLOOKUP(A2,[source.xls]Sheet1!$A$2:$B$7,3,FALSE)

    Now it will reference the department and not the engineer...easy <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  9. #9
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use vloopup in VBA macro (2003)

    I am sorry l am confused , l have made changes but no luck. See attached
    Formulas don't work and l donít know why as not to familiar with VB.
    Attached Files Attached Files

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use vloopup in VBA macro (2003)

    Last chance for you here, I have done a lot of work for you of late especially in the Access without thanks and it appears that you are not following the instructions <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Find attached your workbooks...I'll leave you to work it out and fit it into your working model
    Jerry

  11. #11
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use vloopup in VBA macro (2003)

    Many thanks for that. Most times l do thank people for there help, my apologise if l have not thanked you for any help in any areas you have helped me out with.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •