Macro To Add 'FA'

[Dazzawm]

Is there a macro code I can run that will add 'FA' to the front of any value in a cell in column 'A'? I know I could use a helper column then concatenate etc but there are some blank cells etc and I thought if there is a code someone could give me I would be obliged. Thanks.

 

[VoG]

Try

Sub AddFA()
With Columns("A").SpecialCells(xlConstants, xlTextValues)
    .Value = "FA" & .Value
End With
End Sub

 

[Dazzawm]

Thanks I get runtime 13 error though and it points to
.Value = "FA" & .Value

Not sure if it makes a difference but the values in "A" will be a combo of text and numbers.

 

[VoG]

I can't reproduce that error but my previous code didn't function properly anyway. Try

Sub AddFA()
Dim c As Range
For Each c In Columns("A").SpecialCells(xlConstants, xlTextValues)
    c.Value = "FA" & c.Value
Next c
End Sub

 

[Dazzawm]

Thanks VoG That worked. I don't suppose you know anything about Mail Merge do you, I am getting no replies on this thread.

http://www.mrexcel.com/forum/showthread.php?t=549236

 

[Dazzawm]

I can't reproduce that error but my previous code didn't function properly anyway. Try

Sub AddFA()
Dim c As Range
For Each c In Columns("A").SpecialCells(xlConstants, xlTextValues)
    c.Value = "FA" & c.Value
Next c
End Sub

Thanks VoG this works great but I was thinking...

I will use this quite a lot but with various letters rather than FA. Is there anyway the code can be adapted to when I highlight a column for example column C and run the macro a box would pop saying something like 'what letters would you like added' then i enter the letters, press ok, then the macro runs? Thanks.

 

[VoG]

Try this

Sub AddOwt()
Dim c As Range, s As String
s = InputBox("Enter prefix")
If s = "" Then Exit Sub
For Each c In Selection.SpecialCells(xlConstants, xlTextValues)
    c.Value = s & c.Value
Next c
End Sub

 

[Dazzawm]

Thanks VoG, great stuff. Can you not help with my Mail Merge issue?

 

[pboltonchina]

Hi VoG,

As always a superbly simple solution, how would make the code work with numbers instead of text?