0 votes
in VBA by Beginner (22 points)
edited by

Hi Everyone,

I would like to convert, in Excel, TEXTJOIN to VBA.  For example, selecting cells from one or more columns, and pasting those values to one cell in another column, then sort descending, comma space delimited.

The VBA allows the flexibility of selecting the source range and destination cell via InputBox.

Thanks for any help, Sulgar

Sub Sort_DescendingPasteToCell()
'Select cell range to copy values from, InputBox
'Pastes values to another cell, InputBox
'Sorts pasted values, Descending, comma space delimited

Dim rng As Range
Dim RngTo As Range
Dim RngFrom As Range
Dim RngFromAddress

On Error Resume Next
'User selects cell range values copied from
Set RngFrom = Application.Selection
Set RngFrom = Application.InputBox("Select Range Values to Copy:", , RngFrom.Address, Type:=8)
RngFromAddress = Range(RngFrom).Address

'User selects cell to paste values to:
Set RngTo = Application.Selection
Set RngTo = Application.InputBox("Paste To Cell:", , RngTo.Address, Type:=8)
If RngTo Is Nothing Then
MsgBox "Operation Cancelled"
Else
End If

'Paste values from RngFrom To RngTo cell, sort descending, comma space delimited
' => works in formula bar, for example pre-sorted in column Z, as ="Sorted, Descending: "&TEXTJOIN(", ",TRUE,Z:Z)
'but not working below for VBA, any suggestions?
RngTo.Select
RngTo = "=""Sorted, Descending: ""&TEXTJOIN("", "",TRUE,RngFromAddress)"
End Sub

 

1 Answer

+1 vote
by Super Expert (2.7k points)
selected by
 
Best answer

Your RngTo statement at the bottom of the macro is setting the range you want to join equal to the literal string "RngFromAddress." You'll need to break up your string so the address of your range is entered as variable, like this:

RngTo.Value = "=""Sorted, Descending: ""&TEXTJOIN("", "",TRUE," & RngFromAddress.Address & ")"

Notice this code also uses the .Value property of the RngTo variable instead of setting the formula equal to the entire Range object. You could have also used the .Formula property.

With all that said, if you don't want the RngTo cell to be a formula that changes when the values of RngFromAddress change, you could also call the TextJoin function from the Application.WorksheetFunction property, like this:

RngTo.Value = "Sorted, Descending: " & Application.WorksheetFunction.TextJoin(", ", True, RngFromAddress)

 

by Beginner (22 points)
edited by

Thank you wellsr for the very helpful suggestions. Two different codes were created. One, for the result pasted in Value format. A second, for the result pasted in Formula format. In both cases, the result was pasted to one cell. For any questions, feel free to ask. Thanks again! 

Sulgar

 ::::::::Version 1: Result Pasted in VALUE FORMAT:::::::

Sub Sort_DescendingPasteToCell_AsValueFormat()
'Select cell range to copy values from, InputBox
'Sort Descending by clicking Data (from Menu Bar) ->ZA(down arrow Sort) is one method
'Pastes values to another cell, InputBox
'Values pasted in value format, Sort Descending, comma space delimited

Dim StrOutTrimmed As String
Dim RngFrom As Range
Dim RngTo As Range
Dim Separator As String
Dim Cell As Range
Dim StrOut As String
'Get RngFrom from InputBox HERE
On Error Resume Next
'User selects cell range values copied from
Set RngFrom = Application.Selection
Set RngFrom = Application.InputBox("Select Range Values to Copy:", , RngFrom.Address, Type:=8)

'String listed, comma space delimited
For Each Cell In RngFrom
StrOut = StrOut & Cell.Value & ", " 'Separator
Next Cell
StrOutTrimmed = "Sorted, Descending: " & Left(StrOut, Len(StrOut) - 2)

'Paste string to RngTo from InputBox HERE
'User selects cell to paste values to:
Set RngTo = Application.Selection
Set RngTo = Application.InputBox("Select Cell To Paste As Values:", , RngTo.Address, Type:=8)
If RngTo Is Nothing Then
MsgBox "Operation Cancelled"
Else
End If

'StrOutTrimmed Pasted to RngTo
'Seek to paste in cell as Sort, Descending, comma delimited, value format
RngTo.Value = StrOutTrimmed

End Sub

::::::Version 2: Result Pasted in FORMULA FORMAT:::::::

Sub Sort_DescendingPasteToCell_AsFormula()
'Select cell range to copy values from, InputBox
'Sort Descending by clicking Data (from Menu Bar) ->ZA(down arrow Sort) is one method
'Pastes values to another cell, InputBox
'Values pasted as a Formula, Sort Descending, comma space delimited

Dim rng As Range
Dim RngTo As Range
Dim RngFrom As Range
Dim RngFromAddress

On Error Resume Next
'User selects cell range values copied from
Set RngFrom = Application.Selection
Set RngFrom = Application.InputBox("Select Range Values to Copy:", , RngFrom.Address, Type:=8)
'RngFromAddress = Range(RngFrom).Address    '<----DELETED THIS
RngFromAddress = RngFrom.Address            '<----ADDED THIS

'User selects cell to paste values to:
Set RngTo = Application.Selection
Set RngTo = Application.InputBox("Paste To Cell:", , RngTo.Address, Type:=8)
If RngTo Is Nothing Then
MsgBox "Operation Cancelled"
Else
End If

'Paste values from RngFrom To RngTo cell, sort descending, comma space delimited, formula format
'RngTo.Select
RngTo.Value = "=""Sorted, Descending: ""&TEXTJOIN("", "",TRUE," & RngFromAddress & ")"
End Sub

 

by Super Expert (2.7k points)

Oh okay, I understand what you're trying to do now. When you set RngTo using the Application.InputBox, do you always select only one cell? If so, replace the RngTo line at the bottom with the following VBA code block:

RngTo.Value = "Sorted Descending:, " & Application.WorksheetFunction.TextJoin(", ", True, rngFrom)
Application.DisplayAlerts = False
RngTo.TextToColumns RngTo, xlDelimited, , , , , True 'comma delimited'
Application.DisplayAlerts = True
Set RngTo = RngTo.Resize(, RngTo.End(xlToRight).Column - RngTo.Column + 1) 'resize the range based on number of commas'
RngTo.Sort Key1:=RngTo, Orientation:=xlLeftToRight, order1:=xlDescending ' sort descending'

These lines of code add the data to one cell, splits the cell based on a comma delimiter, then sorts descending.

by Beginner (22 points)
edited by
Thanks wellsr for the quick reply.

With the above lines, the paste to RngTo began at the cell selected from InputBox, but continued in a series of separate consecutive cells to the right.

RngFrom is sourced from at least one cell.  Desired is RngTo in one cell only.
For example, a RngFrom of Z2:Z11, and a RngTo of Z13.
.

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...