+ Reply to Thread
Results 1 to 4 of 4

VBA version of IF function?

  1. #1
    Registered User
    Join Date
    01-25-2019
    Location
    Connecticut, USA
    MS-Off Ver
    2016
    Posts
    10

    Question VBA version of IF function?

    Hi all,

    I am currently trying to concatenate certain cells based on the value of a particular cell (I.e. if A2 = X, concatenate B2, C2, and D2 , but if A2 = Y, concatenate B2, C2, and E2), separated by commas.

    I have managed to do this with an IF function below

    =IF(A2="X",B2&"in, "&C2&", "&D2,IF(A2="Y",B2&"in, "&C2&", "&E2))

    I know in the future I am going to have more values than just x and y, and I will have to concatenate more columns of info based on them, so I didn't want an IF function that was 3 miles long in case someone has to go back and edit they don't have to search through a formula in a cell.

    Is there a way to achieve the same thing in VBA?

    (Also, column B in the table specifies a length in inches which is important in the description (the result of concatenating the cells), is there a way to specify units for one column but not others?)

    Thanks,

    cicicle

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: VBA version of IF function?

    If A2 is only going to be X or Y you could shorten that formula (as it stands) to

    =B2&"in, "&C2&", "&IF(A2="X",D2,E2)

    since in both cases B2 and C2 are always concatenated
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    01-25-2019
    Location
    Connecticut, USA
    MS-Off Ver
    2016
    Posts
    10

    Re: VBA version of IF function?

    That is sort of the problem it is not always going to be x or y, I might find I need to add a z, or even a thru w criteria that select even more cells, and then my if function will keep growing and it will not be easy for a maintenance standpoint to go through the formula and find the parts I want to change and add more criteria.

    That is why I was thinking VBA would be better at least from an organization standpoint, and also automation so I would not have to go and copy the changed formula to all of the cells it would simply update as I change the code.

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,444

    Re: VBA version of IF function?

    Here is a user-defined function (UDF) that will do what you want, and if you know VBA is simple to update.

    The function takes as input the cell that can be X or Y. This will cause it to recalculate when that cells changes, so avoids requiring this to be a volatile function. However that means it will not automatically recalculate if the cells in B, C, D, or E change.

    Code below, example attached.
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. [SOLVED] Formulas work in online Google Docs version but not in offline version
    By virtuosok in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-15-2018, 06:47 AM
  2. Replies: 0
    Last Post: 01-09-2016, 02:02 PM
  3. Formulas not working in higher version, 2010 Version to 2013 version
    By thilag in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-19-2015, 09:09 PM
  4. Replies: 2
    Last Post: 04-11-2015, 12:44 AM
  5. Get code that works on excel version 2007 to work on version 2010
    By Agent1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-21-2014, 01:23 AM
  6. Help with function need short version.
    By miszeq in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-22-2013, 04:55 AM
  7. How to make a more complicated version of the if function
    By darblooz in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-31-2006, 07:25 AM

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