Adding a prefix and or suffix

[Sipsoo]

Hi Group,

I want to create a macro that will inset a ',' at the end of a populated cells i.e.

range A1 to A30

C88085','
A83016','
F86008','
M91021','
P81004','
F86651','
A82008','
A83075','

The cells will be populated manually then I would like to run the macro that will and the suffix to the populated cells.

Is this possible

Thanks in advance


Ken

 

[njimack]

Based on a macro I wrote to convert policy numbers into the correct format for my SQL editor...

Sub replacetext()
Dim Myvalue As Variant
Dim n As Integer


Application.ScreenUpdating = False

With ActiveSheet

n = 1
Do While Len(.Cells(n, 1)) > 0

'Insert  comma and space to each policy number
Myvalue = Cells(n, 1)
Cells(n, 1).Value Myvalue & ", "

 n = n + 1
 Loop

 

[Sipsoo]

Thanks Neil, I'll give it a go...

Ken

 

[erik.van.geit]

Hi, Ken,

try this

Option Explicit

Sub test()
Dim rng As Range
Dim arr As Variant
Dim LR As Long
Dim i As Long
Dim j As Integer

    With ActiveSheet
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = .Range("A1:A" & LR)
    End With

arr = rng

    For i = 1 To rng.Rows.Count
    arr(i, 1) = arr(i, 1) & ","
    Next i

rng = arr
Erase arr

End Sub

for more info about this technique see http://puremis.net/excel/code/053.shtml

kind regards,
Erik

 

[Sipsoo]

Many thanks Eric... got two bits of code to play with now...

Ken

 

[erik.van.geit]

have a nice play, but think about reading the information
it's about speed