VBA help to copy text to clipboard

[saiyamvora13]

Hello,
Here is the Macro that I am using
Sub Selected_to_Comma_Separated()


Dim MyRange As Range
Dim NewRange As Range
Dim arr
Dim clip As MSForms.DataObject

For Each MyRange In Selection
If MyRange.Value <> "" Then
MyRange.Value = Chr(39) & Chr(39) & MyRange.Value & Chr(39)
End If
Next MyRange


Set NewRange = Selection

arr = Join(Application.Transpose(NewRange.Value), ",")

Set clip = New MSForms.DataObject
clip.SetText arr
clip.PutInClipboard

MsgBox "Copied to clipboard: " & Chr$(10) & arr

End Sub

What it does:
Anything that is selected on the excel sheet, it will add quotations in the start(') and end(') and it will add a comma (,) and then copy it to the clipboard
Example (al033j) will become ('al033f',)
Issue is that if I press the macro button the second time, the selection now becomes (''al033f'',)
If i press it 3rd time it becomes ('''al033f''',)
If I press it 4th time it becomes (''''al033f'''',)
This keeps going on.

Is there anyway for me to keep it at ('al033f',) no matter how many times I press the button?

Thank you for all your help

 

[tonyyy]

You might try the following modification...

[COLOR=#808080]For Each MyRange In Selection
    [/COLOR][COLOR=#0000ff]If MyRange.Value <> "" And MyRange.Value <> Chr(39) & Chr(39) & MyRange.Value & Chr(39) & "," Then[/COLOR][COLOR=#808080]
        MyRange.Value = Chr(39) & Chr(39) & MyRange.Value & Chr(39)
    End If
Next MyRange[/COLOR]

Cheers,

tonyyy

 

[saiyamvora13]

Hi Tonyy, this did not work
When I run the macro a second time, it adds the quotes again to the selection making it ('''al033f''',)

Please let me know if you have a better solution.

 

[tonyyy]

saiyamvora13,

I'm assuming when you say "adds the quotes" you really mean apostrophes.

Anyway, the approach below deletes any existing apostrophes before adding new ones.

Sub Selected_to_Comma_Separated2()

Dim MyRange As Range
Dim NewRange As Range
Dim arr
Dim clip As MSForms.DataObject

For Each MyRange In Selection
    If MyRange.Value <> "" Then
        MyRange.Value = Replace(MyRange.Value, "'", "")
        MyRange.Value = Chr(39) & Chr(39) & MyRange.Value & Chr(39)
    End If
Next MyRange

Set NewRange = Selection

arr = Join(Application.Transpose(NewRange.Value), ",")

Set clip = New MSForms.DataObject
clip.SetText arr
clip.PutInClipboard

MsgBox "Copied to clipboard: " & Chr$(10) & arr

End Sub

 

[saiyamvora13]

Thanks so much tonyy, this worked

 

[tonyyy]

You're welcome. Glad it worked out...

 

[saiyamvora13]

Hi Tony, I just posted a new question and given your knowledge with excel, this might be a breeze for you. Can you please look at.

http://www.mrexcel.com/forum/excel-questions/965041-average-funtion-excel-top-5-values.html