+ Reply to Thread
Results 1 to 6 of 6

VBA: Insert OR between Range

  1. #1
    Forum Contributor
    Join Date
    02-16-2006
    Posts
    218

    VBA: Insert OR between Range

    I'm needing assistance creating some VB code to insert OR between numbers listed in an input range. The formula to do what I am looking for would be: =CONCATENATE(TRANSPOSE(A1:A7)&" OR ")

    Note: I'm looking for a prompt to input the range.

    Thanks,

    Met

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,589

    Re: VBA: Insert OR between Range

    Do you have Excel 2016 or later? Your details neither show your location nor which software versions you have. these are often useful when formulating solutions. Please add them.

    If you have 2016+ then the new TEXTJOIN() or CONCAT() functions may help. With CONCATENATE you can't select a range, you can only specify individual cells.

    However upload an example workbook and add the results you expect to see and we can probably assist further.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    02-16-2006
    Posts
    218

    Re: VBA: Insert OR between Range

    Thanks for the response. (See attached) Please note this is merely an example and the range will always vary.

    MET
    Attached Files Attached Files

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,589

    Re: VBA: Insert OR between Range

    There are two options in the attached workbook

    1. Formula in E2 copied down to E8
    Copy E8 and Paste as a value to E10
    Use the formulae in E11 & E12 to create the final string

    2. A VBA Function
    Please Login or Register  to view this content.
    In any cell add

    =JOIN(A2:A8)
    Attached Files Attached Files

  5. #5
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,249

    Re: VBA: Insert OR between Range

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    02-16-2006
    Posts
    218

    Re: VBA: Insert OR between Range

    Thanks for everyones help.

+ 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] How to insert items in column A into a range of cells and copying the range
    By arbazhamayun in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-06-2018, 03:44 PM
  2. Insert rows in between a range from another range based on dates
    By siprem in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2016, 09:01 AM
  3. Replies: 1
    Last Post: 03-02-2015, 11:18 AM
  4. [SOLVED] Insert Row throws error "Insert Method of Range class failed"
    By bg_enigma1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-09-2015, 11:30 AM
  5. Help - Insert method of Range class failed - Selection.Insert Shift:=xlToRight
    By sandy@excelforum in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-04-2012, 11:14 AM
  6. Replies: 3
    Last Post: 06-22-2010, 07:17 PM
  7. insert a row every nth row and sum range
    By bsomers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2007, 06:52 PM

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