use macro to insert characters at the beginning of a string

[dcmackie]

I thought his would be the simplest of macros !

I need to add "19" to the beginning of the description of an asset to
correct two digit years to four.

I recorded my steps ie. F2 key , then home then right arrow the 19 then an
enter key to move down to the next cell.

If the next cell needs correction and I run the macro created by my
recording, rather than correcting the new cell, ie. adding "19" to the
beginning, it inserts the corrected contents of the previous cell on which
the macro was run.

Engaging the relative reference key does not help.

 

[Don Guillett]

try
Sub addleading19()
For Each c In Selection
c.Value = "19" & c
Next
End Sub

 

[David McRitchie]

and the reason why what was done does not work is that
keystrokes like what you do to the formula bar is not recorded
only the resulting value (or formula). Recording a macro is
good for finding out what kind of instructions might help they
rarely can be used as generated.

You should have Option Explicit at the beginning of your
module in which case you will need to include the following
at the beginning of your macro. This will help you from
misusing code and to provide more meaningful error descriptions.

Dim c as Range

Because nothing in the macro is done to limit the scope within
the selection, your selection would have to include only the cells
you want to process, as opposed to selecting an entire column,
for instance.

Just in case you don't get exactly the kind of value you wanted:
Placing a "19" in front is making an assumption that your value
is a text string and you want the result as a text string as opposed
to a number. Since you only indicated your change was to the
wrong cell, I don't expect you actually have a problem in this regard.

 

[Don Guillett]

David, I did test with a number and text and number only.

Sub addleading19()
For Each c In Selection
c.Value = "19" & c
Next
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)

and the reason why what was done does not work is that
keystrokes like what you do to the formula bar is not recorded
only the resulting value (or formula). Recording a macro is
good for finding out what kind of instructions might help they
rarely can be used as generated.

You should have Option Explicit at the beginning of your
module in which case you will need to include the following
at the beginning of your macro. This will help you from
misusing code and to provide more meaningful error descriptions.

Dim c as Range

Because nothing in the macro is done to limit the scope within
the selection, your selection would have to include only the cells
you want to process, as opposed to selecting an entire column,
for instance.

Just in case you don't get exactly the kind of value you wanted:
Placing a "19" in front is making an assumption that your value
is a text string and you want the result as a text string as opposed
to a number. Since you only indicated your change was to the
wrong cell, I don't expect you actually have a problem in this regard.
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

try
Sub addleading19()
For Each c In Selection
c.Value = "19" & c
Next
End Sub

 

[David McRitchie]

Hi Don,
I guess I used too many you's referring to two different
people. But there is the *possibility* that what he had
were dates, or that "numbers" had leading zeros.
In any case I would not want to encourage anyone
to remove Option Explicit
from a module, because the posting sounded like he
was new to macros but know how to install and use.

David, I did test with a number and text and number only.

Sub addleading19()
For Each c In Selection
c.Value = "19" & c
Next
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)

and the reason why what was done does not work is that
keystrokes like what you do to the formula bar is not recorded
only the resulting value (or formula). Recording a macro is
good for finding out what kind of instructions might help they
rarely can be used as generated.

You should have Option Explicit at the beginning of your
module in which case you will need to include the following
at the beginning of your macro. This will help you from
misusing code and to provide more meaningful error descriptions.

Dim c as Range

Because nothing in the macro is done to limit the scope within
the selection, your selection would have to include only the cells
you want to process, as opposed to selecting an entire column,
for instance.

Just in case you don't get exactly the kind of value you wanted:
Placing a "19" in front is making an assumption that your value
is a text string and you want the result as a text string as opposed
to a number. Since you only indicated your change was to the
wrong cell, I don't expect you actually have a problem in this regard.
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

try
Sub addleading19()
For Each c In Selection
c.Value = "19" & c
Next
End Sub

I need to add "19" to the beginning of the description of an asset to
correct two digit years to four.

I recorded my steps ie. F2 key, then home then right arrow the 19 then an
enter key to move down to the next cell.

If the next cell needs correction and I run the macro created by my
recording, rather than correcting the new cell, ie. adding "19" to the
beginning, it inserts the corrected contents of the previous cell on which
the macro was run.

Engaging the relative reference key does not help.