I wish to make a macro in vba, to join the text in a selected range, substitute all the spaces with %20, and add |, after every cell. I wish to have this result in the cell next to the last cell with data in the row I am working in, since I am only going to be selecting cells in a row.
I have typed some code, but it doesn't seem to work, can someone please help me?
I have typed some code, but it doesn't seem to work, can someone please help me?
Code:
Function wMerge(Rng As Range)
Dim col1 As Integer
Dim col2 As Integer
Dim total As Integer
Dim subs As String
Dim concat1 As String
Dim concat2 As String
Dim s As String
part1 = Left(Rng.Address, Application.WorksheetFunction.Find("-", Rng.Address) - 1)
part2 = Right(Rng.Address, Len(Rng.Address) - Application.WorksheetFunction.Find("-", Rng.Address))
col1 = Range(Left(part1, 1)).Column
col2 = Range(part2).Column
s = Range(part2).Address(False, False)
ColumnName = Left(s, Len(s) - Len(Format(Rng.Row, "0")))
For i = 1 To total
concat1 = Replace(ActiveSheet.Cells(Range(part1).Row, total).Value, " ", "%20")
concat2 = concat1 & "|"
Next i
ActiveSheet.Cells(total, Range(part2).Row).Value (concat2)
End Function