PDA

View Full Version : [SOLVED] Combine strings and place in separate sheet



dumplingmast
03-15-2019, 08:21 AM
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.

JKwan
03-15-2019, 08:45 AM
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

dumplingmast
03-15-2019, 09:05 AM
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.

JKwan
03-15-2019, 09:22 AM
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

dumplingmast
03-15-2019, 12:33 PM
Thanks much, JKwan.