+ Reply to Thread
Results 1 to 6 of 6

Goal; Concatenating 5 dynamic columns WITH spaces

  1. #1
    Registered User
    Join Date
    09-23-2015
    Location
    NY
    MS-Off Ver
    2013
    Posts
    3

    Talking Goal; Concatenating 5 dynamic columns WITH spaces

    Hi Guys,

    New to the community so please let me know if I'm missing any
    Please Login or Register  to view this content.
    Essentially, I have the columns defined as integers (not sure if it affects it here) and I'd like to add spaces or underscores between them. I just need to know what to add into my FormulaR1C1 = row.

    Thanks for taking a look. Let me know if you have any questions.

    Best,
    Louis
    Last edited by FDibbins; 09-23-2015 at 05:36 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Goal; Concatenating 5 dynamic columns WITH spaces

    Hi, welcome to the forum

    (I have added the code tags for your code - this time )

    This is a formula that I have used to insert commas when combining cells (including dealing with blank cells). Perhaps you can modify it for your code?
    =SUBSTITUTE(TRIM(B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10)," ",",")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    09-23-2015
    Location
    NY
    MS-Off Ver
    2013
    Posts
    3

    Re: Goal; Concatenating 5 dynamic columns WITH spaces

    Thanks for the speedy reply and adding my tags! Noted for next time

    After replacing your cells with my defined columns, I'm now getting a compile error because it expects a list separator or ) between the first " ".

    I'm thinking the error is because my module primarily defines object variables. I thought it would be easier to just add the underscore to my FC formula, I'm just having trouble with the syntax. What do you think?

    Please Login or Register  to view this content.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Goal; Concatenating 5 dynamic columns WITH spaces

    1) Range/Cells object must be qualified, otherwise you will get error when the activesheet is not wksDest.
    Please Login or Register  to view this content.
    2) Add "" ""& or ""_""& to your formula
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-23-2015
    Location
    NY
    MS-Off Ver
    2013
    Posts
    3

    Re: Goal; Concatenating 5 dynamic columns WITH spaces

    THANK YOU jindon!!! The code in #2 did the trick!

    And you as well FDibbins! This community is fantastic. Happy to add to your reputation, let me know if there's anyway I can return the favor!

    Thanks again,
    Louis

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Goal; Concatenating 5 dynamic columns WITH spaces

    Im just happy you got your question resolved, thanks for the kind words and the feedback

+ 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. Goal Seek function in a dynamic excel sheet
    By tonisjoseph in forum Excel General
    Replies: 4
    Last Post: 01-16-2015, 12:48 AM
  2. VBA For Concatenating Dynamic Rows and Columns
    By imark in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-15-2014, 02:05 PM
  3. [SOLVED] Concatenating two columns and reversing after concatenating the result
    By irfanparbatani in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-22-2014, 09:35 PM
  4. [SOLVED] Goal Seek Dynamic Range
    By The Cardman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-12-2012, 02:27 AM
  5. Goal Seek Dynamic Range
    By The Cardman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-12-2012, 02:05 AM
  6. [SOLVED] Issues with Concatenating when a cell contains 287 spaces
    By DDR in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-22-2012, 05:45 PM
  7. Concatenating columns
    By ArtisanInternet in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-13-2008, 07:10 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