Need help with a Marco for excel

[JTILLOTSON]

I have a column filled with information that goes down little over 1500 rows. I need to add the letter s to the beginning of all the text in those rows. I am not very good with macros but thought they would be able to help me.

What do I need to do to create this macro?

Thanks!

 

[rmikesmith]

Assume your data is in column A, from row 1 to 1500. The following procedure will add an "s" to the beginning of each cell:

Sub AddS()
Dim OneCell As Range

   For Each OneCell In ActiveSheet.Range("A1:A1500")
     OneCell.Value = "s" & OneCell.Text
   Next OneCell

End Sub

Regards,
Mike

 

[RonRepp]

Try this:
========================
Sub AddS()
''Make sure you put your pointer where you want to start
ActiveCell.Value = "S" & ActiveCell.Value
Do
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Text = "" Then Exit Sub
ActiveCell.Value = "S" & ActiveCell.Value
Loop Until ActiveCell.Text = ""
End Sub
=========================

I hope this helps.

Ron

Ron Repp

 

[Bong]

something like this maybe

    Dim r1 As Range
    Set r1 = Range("A1:A1500")
    For Each r In r1.Rows
        Cells(r.Row, 1).Value = "s" & Cells(r.Row, 1).Value
    Next

_________________
Bob Rashkin

 

[AnotherHiggins]

I avoid looping code whenever possible.

For 1500 rows the following code should run faster.

Let's say that your data is in column A and that you have headers in the first row. If column B is empty, then you could use:

Sub Add_S()
Dim myRng As Range
Set myRng = Range([B2], [A1].End(xlDown).Offset(, 1))

myRng = "=""s""&RC[-1]"
myRng.Copy

[A2].PasteSpecial Paste:=xlValues
Columns("B:B").Delete

This makes use of a simple excel worksheet function, then copies and pastes the values over the original data.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mintjulep]

Really no need for a macro.

Assume your existing data is in column C.

Insert a new column to the right of C. This will become D.

In D1 enter "="s" & C1

Copy that down.

Highlight column D and copy.

Paste special that over the original column C as values only.

Delete column D.

This is what anotherhiggins' code does.