+ Reply to Thread
Results 1 to 8 of 8

Select Case Code for Column A Values to Populate Results in Column B in Same Row

  1. #1
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Select Case Code for Column A Values to Populate Results in Column B in Same Row

    Hello and thanks in advance.

    I'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").

    I think I'm close, but my struggles I believe are in coding the destination range since I can get it to work for just one cell in B!

    Below is my code that is not working...

    Please Login or Register  to view this content.
    Any help is much appreciated! Thanks, Chris

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Select Case Code for Column A Values to Populate Results in Column B in Same Row

    Chris

    There's no loop, give this a try.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Thumbs up Re: Select Case Code for Column A Values to Populate Results in Column B in Same Row

    Nice, you're awesome Norie! Thanks, works great! I was trying to think of how to use offset, although I would have never come up with it. Another of many lessons I've received here!

    In gratitude,
    Chris

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

    Re: Select Case Code for Column A Values to Populate Results in Column B in Same Row

    Maybe:

    Please Login or Register  to view this content.
    OOPS - I didn't know that Norie had already answered
    Last edited by xladept; 05-28-2014 at 03:05 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

  5. #5
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Question Re: Select Case Code for Column A Values to Populate Results in Column B in Same Row

    Thanks xladept...great minds apparently!

    One thing I've noticed is that while the code works, it seems to be bit slow (in Excel terms anyways) and takes about 10 seconds or so to roll through all the cases and rows. Any ideas on how to make it run faster? Not all the rows will have entries, I'm curious if there's a snipet of code to say "at the first blank row, exit the sub" or set the range to be more dynamic by identifying the range in column A where it starts at A14, finds the last entry (say A25) and then runs on that range.

    Thoughts?

    Thanks,
    Chris

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Select Case Code for Column A Values to Populate Results in Column B in Same Row

    This might be quite quick.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Re: Select Case Code for Column A Values to Populate Results in Column B in Same Row

    Wow! Lightning quick (like your response time)! Using it with Worksheet_Activate (since the A range prefills before a user goes to that sheet) and I couldn't even see it fill in. Sweet. Thanks much Norie!

    Chris

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

    Re: Select Case Code for Column A Values to Populate Results in Column B in Same Row

    Thanks for grouping me with Norie - very gratifying

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA code to select random values from a column and insert them in another sheet
    By Hellga in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2013, 02:16 PM
  2. [SOLVED] how to set select case values from sheet 2 vba code
    By sspreyer in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 08-29-2013, 12:27 PM
  3. Replies: 3
    Last Post: 01-06-2011, 01:16 AM
  4. capture unique values from 1 column and populate the result into another column
    By teachMeExcel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-28-2009, 05:21 PM
  5. [SOLVED] Populate a column by extracting unique values from another column?
    By Mike Palmer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

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