character adding

[aoc]

hi,

there are numbers in different cells. how can I add a character to the beginning of these numbers easily.

for example

7665m007
7874dt33
2011kf85

will be
d7665m007
d7874dt33
d2011kf85

[anandbohra]

paste the code in module & run
(pl change the sheet & range reference accordingly)

[VBA]Sub addcharacter()
Application.ScreenUpdating = False
Dim iLastRow As Long
Dim i As Long
Dim addchar As String
addchar = "d" '--Change as per requirement
With Sheet1 '--Change as per requirement
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
.Cells(i, "A") = addchar & .Cells(i, "A")

Next i

End With
Range("a1").Select
Application.ScreenUpdating = True
End Sub[/VBA]

[aoc]

Hi,

I tried but I get object required error. can you please check from the attached file

[mdmackillop]

In a spare column enter ="d" & A1 and copy down. Copy/PasteSpecial Values over the original data and clear the helper column.

[mdmackillop]

For i = iLastRow To 1 Step -1

No need to step backwards as you are not inserting/deleting rows.

[aoc]

hi,

if I want to add * symbol after 4 and delete k in g1204k

it will be g1204*. how can I do ?

[mdmackillop]

Check out Replace in Help.

[anandbohra]

hi,

if I want to add * symbol after 4 and delete k in g1204k

it will be g1204*. how can I do ?

Just replace existing line with the below one

[VBA].Cells(i, "A") = addchar & Left(.Cells(i, "A"), Len(.Cells(i, "A")) - 1) & "*"[/VBA]

the above code first delete last 1 character then add given character on starting & asterisk (*) at the end

[Krishna Kumar]

paste the code in module & run
(pl change the sheet & range reference accordingly)

[vba]Sub addcharacter()
Application.ScreenUpdating = False
Dim iLastRow As Long
Dim i As Long
Dim addchar As String
addchar = "d" '--Change as per requirement
With Sheet1 '--Change as per requirement
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
.Cells(i, "A") = addchar & .Cells(i, "A")

Next i

End With
Range("a1").Select
Application.ScreenUpdating = True
End Sub[/vba]

You could avoid looping. See

[vba]Sub kTest()
Dim r As Range, f As String
Set r = Range("a1", Range("a" & Rows.Count).End(xlUp))
f = "=""D""&" & r.Address & ""
With r
.Value = Evaluate(f)
End With
End Sub[/vba]

HTH

[mdmackillop]

You could avoid looping. See

[vba]Sub kTest()
Dim r As Range, f As String
Set r = Range("a1", Range("a" & Rows.Count).End(xlUp))
f = "=""D""&" & r.Address & ""
With r
.Value = Evaluate(f)
End With
End Sub[/vba]

HTH

Neat