Excell VBA Macro with Symbols

[Lynette Olivier]

I need to take data out of column A and put that with all commas but i
need to add the following symbol ', example

11111
11112
11113
11114

I need to create a macro that takes this column and create it as follows
11111,11112,11113,11114

but i need the symbol added so that it looks as follows:
'11111','11112','11113','11114'

 

[Stefi]

Sub test()
Dim colA As Range, cA As Range
Set colA = Range("A1:A4")
result = "'"
For Each cA In colA
result = result & cA & "','"
Next cA
result = Left(result, Len(result) - 2)
Range("B1").Value = result
End Sub

Adjust Range("A1:A4")!
It puts the result in B1!

Regards,
Stefi

„Lynette Olivier†ezt írta:

 

[Peter T]

Another one, adapt to your needs -

Sub test()
Dim nRows As Long, nCols As Long
Dim s As String, sMsg As String
Dim rng As Range
Dim arr

Set rng = Range("A1:A4") ' < change, eg set rng=Selection

With rng
nRows = .Rows.Count
nCols = .Columns.Count
arr = .Value
End With

If nRows > 1 And nCols > 1 Then
sMsg = "Only select cells in a single row or column"
ElseIf nRows = 1 And nCols = 1 Then
sMsg = "Only one cell selected"
ElseIf nRows > 256 Then
sMsg = "too many cells selected"
ElseIf nCols = 1 Then
arr = Application.Transpose(arr)
End If

If Len(sMsg) Then
MsgBox sMsg
Else
s = Join(arr, ",")
If IsNumeric(s) Then s = "'" & s
Range("B1") = s
End If

End Sub


Regards,
Peter T