Consulting

Results 1 to 5 of 5

Thread: Combine strings and place in separate sheet

  1. #1

    Combine strings and place in separate sheet

    How to do this in VBA:

    In Sheet1:
    String 1 Read
    String 2 Write
    String 3 Write
    String 4 Read
    String 5 Read

    In Sheet2, how would I get the output in cell A1 to be:
    String 1 - Read
    String 2 - Write
    String 3 - Write
    String 4 - Read
    String 5 - Read

    There are returns between each line.

  2. #2
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    try this
    Sub blah()
        Dim WSInput As Worksheet
        Dim WSOutput As Worksheet
        
        Set WSInput = ThisWorkbook.Worksheets("Sheet1")
        Set WSOutput = ThisWorkbook.Worksheets("Sheet2")
        
        With WSOutput
            For i = 1 To 5
                temp = temp & WSInput.Cells(i, 1) & " - " & WSInput.Cells(i, 2) & vbCrLf
            Next i
            .Cells(1, 1) = temp
        End With
        Set WSInput = Nothing
        Set WSOutput = Nothing
        
    End Sub

  3. #3
    Thanks, JKwan. A couple additional conditions:
    1. How do I account for variable number of rows?
    2. Is there a way to not have the return at the very end?

    Much appreciated.

  4. #4
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    here is the updated code
    Sub blah()
        Dim WSInput As Worksheet
        Dim WSOutput As Worksheet
        Dim LastRow As Long
        
        Set WSInput = ThisWorkbook.Worksheets("Sheet1")
        Set WSOutput = ThisWorkbook.Worksheets("Sheet2")
        LastRow = FindLastRow(WSInput, "A")
        With WSOutput
            For i = 1 To LastRow
                temp = temp & WSInput.Cells(i, 1) & " - " & WSInput.Cells(i, 2) & vbCrLf
            Next i
            .Cells(1, 1) = Left(temp, Len(temp) - 1)
        End With
        Set WSInput = Nothing
        Set WSOutput = Nothing
        
    End Sub
    Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
        FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
    End Function

  5. #5
    Thanks much, JKwan.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •