+ Reply to Thread
Results 1 to 4 of 4

Combine values across columns into 1 text string

Hybrid View

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    Maine
    MS-Off Ver
    Excel 2010
    Posts
    45

    Combine values across columns into 1 text string

    I have been having difficulty being able to combine the text in the applicable row from columns 8 to lastRow into one text string that will then be written to a file. The line I am stuck on is F$I also want to round the values in those cells to be 3 digits long. Please help me figure out my error and clean this up. Thank you.

    Set WS = Worksheets(1)
    
    Pin = InputBox("What is the project pin?", _
            "File Location by Pin Number", "12345")
    
    MyPath = "C:\pin\" & Pin
    
    StartStation = InputBox("What station does the project start at?", _
            "Station by 50ft increments", "1000")
    
    Direction = InputBox("Upchain(50) or Downchain(-50)?", _
            "Direction", "50")
    
    With WS
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        FF = FreeFile
        Open MyPath & "_" & Direction & ".txt" For Output As #FF
        Print #FF, Pin & "                       111042915A 482C1  50 23008823 500000000  " & StartStation
        For A = 2 To LastRow 'label rows as A, A=1 at row 2
        i = 0
            If .Cells(A, 5) > 0 Then
    
                For B = 8 To LastCol 'label columns as B, B=1 at column H
                    
                    Offset = .Cells(1, B) - .Cells(1, 8)
                                           
                    If .Cells(2, B) <> "" Then
                       
                    C$ = "2  " & StartStation
                    D$ = "3  1"
                    E$ = "5 1"
                        Set rang = Range(.Cells(A, 8), .Cells(A, B))
                        CurSta = StartStation + (Direction * i)
                    F$ = "G  " & CurSta & "  65  0999" _
                        & combine(rang, "")
                        G$ = "B  1"
                    Print #FF, C$
                    Print #FF, D$
                    Print #FF, E$
                    Print #FF, F$
                    Print #FF, G$
                           
                    End If
                Next
            End If
            i = i + 1
        Next
        Close #FF
    End With
    
    Function combine(rng As Range, Optional delim As String = "") As String
        combine = Join(Application.Transpose(Application.Transpose(rng)), delim)
    End Function
    Last edited by cnash52; 02-25-2016 at 12:04 PM.

  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Combine values across columns into 1 text string

    Your combine function appears to return a two dimensional array. Therefore it cannot simply be concatenated into a string.

    It looks like the second dimension has a size of 1.


    click on the * Add Reputation if this was useful or entertaining.

  3. #3
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: Combine values across columns into 1 text string

    The line I am stuck on is F$.
    What's the problem here?
    I also want to round the values in those cells to be 3 digits long.
    You might try
    combine = Join(Application.Transpose(Application.Transpose(rng.Text)), delim)
    and make sure the format is set in the cell, else you'll have to iterate the whole range and use the VBA format function.
    David
    (*) Reputation points appreciated.

  4. #4
    Registered User
    Join Date
    09-13-2012
    Location
    Maine
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Combine values across columns into 1 text string

    Okay so to simplify here is a method I am getting to work but I can't figure out an easier method to loop instead of if statement after if statement from -1.81 to +1.81

                For B = 8 To LastCol 'label columns as B, B=1 at column H
                    'Enter 39 laser readings to string
                    If (.Cells(2, B) <> "") And (.Cells(1, B).Value = -1.81) Or (.Cells(1, B).Value = -1.8) Then
                            Line = Line + CStr((.Cells(2, B)))
                    End If
                    If (.Cells(2, B) <> "") And (.Cells(1, B).Value = -1.71) Or (.Cells(1, B).Value = -1.7) Then
                            Line = Line + CStr((.Cells(2, B)))
                    End If
                    If (.Cells(2, B) <> "") And (.Cells(1, B).Value = -1.61) Or (.Cells(1, B).Value = -1.6) Then
                            Line = Line + CStr((.Cells(2, B)))
                    End If
                    If (.Cells(2, B) <> "") And (.Cells(1, B).Value = -1.51) Or (.Cells(1, B).Value = -1.5) Then
                            Line = Line + CStr((.Cells(2, B)))
                    End If

+ 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. combine columns with text, move over columns with number
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-30-2015, 02:58 PM
  2. [SOLVED] Combine 2 text columns
    By Fentoei in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-27-2014, 07:05 AM
  3. MAcro to - copy, transpose, text to columns, remove column A, combine all columns in 1
    By galvinpaddy in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-07-2013, 08:48 AM
  4. Multiple Column Lookup Needs to combine formatting for Percentage and Text String.
    By Cardinalbags in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2012, 09:47 AM
  5. Combine contents of two columns of text into one column of text?
    By sammyandco in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-27-2012, 01:04 AM
  6. Replies: 16
    Last Post: 08-21-2011, 11:08 PM
  7. Combine 2 Text Columns
    By blizad1 in forum Excel General
    Replies: 2
    Last Post: 09-22-2010, 07:24 PM

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