+ Reply to Thread
Results 1 to 6 of 6

VBA lookup a name based on a number

  1. #1
    Registered User
    Join Date
    10-24-2017
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    9

    VBA lookup a name based on a number

    Hello everyone
    Hope you can help since i'm new to VBA
    I want to create a macro that has similarities to the Vlookup function.
    The reason for this (illustrated in the Excel sheet) is that i have a list of different codes and the associated names (column F & G). In column B the user then inputs the code, and the macro should then automatically report the associated name in column C. If the user enters a code that doesn't have a name associated it should mark the column C cell as red (see example in sheet)

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,717

    Re: VBA lookup a name based on a number

    Try adding this code to the Sheet1 tab

    Please Login or Register  to view this content.
    Martin

    If my solution has saved you time and/or money, please consider donating to Cancer Research UK or your local equivalent.

    https://www.cancerresearchuk.org/

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    10,550

    Re: VBA lookup a name based on a number

    Paste this event code in your sheet module and save your workbook as macro-enabled:

    Please Login or Register  to view this content.
    Last edited by xladept; 01-03-2018 at 04:15 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Registered User
    Join Date
    10-24-2017
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    9

    Re: VBA lookup a name based on a number

    Hello mrice
    Tried to run your code and it worked almost perfectly. When i entered a number it showed me the associated name as supposed to. I then tried to press delete to clear the cell, but the name cell still remains red can this be changed somehow?
    Furthermore, i forgot that cells containing the code and associated name (column 6 and 7) is located in sheet 2, but the input function is located in sheet 1. Can this be changed aswell?

    Again thank you for the code! it's really appreciated

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    10,550

    Re: VBA lookup a name based on a number

    So you can delete an entry:

    Please Login or Register  to view this content.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    10,550

    Re: VBA lookup a name based on a number

    Thanks for the rep!

    Have you a different sample file???

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1