Adding a prefix and or suffix

Sipsoo

New Member
Joined
Jul 12, 2006
Messages
19
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Based on a macro I wrote to convert policy numbers into the correct format for my SQL editor...

Code:
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Ken,

try this
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,881
Messages
5,543,008
Members
410,583
Latest member
gazz57
Top