| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Insert text in a cell with a macro
I have a series of numbers (SSN) that I need to turn into text, so I want a
macro that will put an apostrophe and a zero at the beginning of the numbers. I know that there are other ways to change a number to text (formatting, find/replace to add the apostrophe) but for what we are doing, manually editing to add the apostrophe and zero is the only thing that works. SO.. to my question. When I make the macro to do this, of course it simply adds the apostrophe and zero and repeats the number from the first cell over and over again. How can I make it select each cell's new data, move to the beginning of the number and add the apostrophe? Here is my macro that is obviously not working. ActiveCell.FormulaR1C1 = "'012365564" ActiveCell.Offset(1, 0).Range("A1").Select I see that the problem is that it is picking up the literal from the first line, but how can I make the macro edit the new numbers in each line and not repeat this text over and over. |
|
#2
|
|||
|
|||
|
iLatRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = 1 To iLastRow Cells(i,"A").Value = "'0" & C ells(i,"A").VAlue Next i -- HTH Bob Phillips "kayabob" wrote in message ... I have a series of numbers (SSN) that I need to turn into text, so I want a macro that will put an apostrophe and a zero at the beginning of the numbers. I know that there are other ways to change a number to text (formatting, find/replace to add the apostrophe) but for what we are doing, manually editing to add the apostrophe and zero is the only thing that works. SO.. to my question. When I make the macro to do this, of course it simply adds the apostrophe and zero and repeats the number from the first cell over and over again. How can I make it select each cell's new data, move to the beginning of the number and add the apostrophe? Here is my macro that is obviously not working. ActiveCell.FormulaR1C1 = "'012365564" ActiveCell.Offset(1, 0).Range("A1").Select I see that the problem is that it is picking up the literal from the first line, but how can I make the macro edit the new numbers in each line and not repeat this text over and over. |
|
#3
|
|||
|
|||
|
Excellent! Thank you. It errored on first try, but I changed to LatRow to
LastRow and C ells to remove the space and it worked perfectly. I bet your fingers were flying so that you could give me a quick answer! "Bob Phillips" wrote: iLatRow = Cells(Rows.Count,"A").End(xlUp).Row For i = 1 To iLastRow Cells(i,"A").Value = "'0" & C ells(i,"A").VAlue Next i -- HTH Bob Phillips "kayabob" wrote in message ... I have a series of numbers (SSN) that I need to turn into text, so I want a macro that will put an apostrophe and a zero at the beginning of the numbers. I know that there are other ways to change a number to text (formatting, find/replace to add the apostrophe) but for what we are doing, manually editing to add the apostrophe and zero is the only thing that works. SO.. to my question. When I make the macro to do this, of course it simply adds the apostrophe and zero and repeats the number from the first cell over and over again. How can I make it select each cell's new data, move to the beginning of the number and add the apostrophe? Here is my macro that is obviously not working. ActiveCell.FormulaR1C1 = "'012365564" ActiveCell.Offset(1, 0).Range("A1").Select I see that the problem is that it is picking up the literal from the first line, but how can I make the macro edit the new numbers in each line and not repeat this text over and over. |
|
#4
|
|||
|
|||
|
No, it is because there are so many questions of this nature that I just
air-code them, and spell-checker doesn't catch those :-). I should know better. -- HTH Bob Phillips "kayabob" wrote in message ... Excellent! Thank you. It errored on first try, but I changed to LatRow to LastRow and C ells to remove the space and it worked perfectly. I bet your fingers were flying so that you could give me a quick answer! "Bob Phillips" wrote: iLatRow = Cells(Rows.Count,"A").End(xlUp).Row For i = 1 To iLastRow Cells(i,"A").Value = "'0" & C ells(i,"A").VAlue Next i -- HTH Bob Phillips "kayabob" wrote in message ... I have a series of numbers (SSN) that I need to turn into text, so I want a macro that will put an apostrophe and a zero at the beginning of the numbers. I know that there are other ways to change a number to text (formatting, find/replace to add the apostrophe) but for what we are doing, manually editing to add the apostrophe and zero is the only thing that works. SO.. to my question. When I make the macro to do this, of course it simply adds the apostrophe and zero and repeats the number from the first cell over and over again. How can I make it select each cell's new data, move to the beginning of the number and add the apostrophe? Here is my macro that is obviously not working. ActiveCell.FormulaR1C1 = "'012365564" ActiveCell.Offset(1, 0).Range("A1").Select I see that the problem is that it is picking up the literal from the first line, but how can I make the macro edit the new numbers in each line and not repeat this text over and over. |
|
#5
|
|||
|
|||
|
For a non-Macro (?micro?) formula use:
=CHAR(39)&"0"&A1 -- Gary's Student "Bob Phillips" wrote: iLatRow = Cells(Rows.Count,"A").End(xlUp).Row For i = 1 To iLastRow Cells(i,"A").Value = "'0" & C ells(i,"A").VAlue Next i -- HTH Bob Phillips "kayabob" wrote in message ... I have a series of numbers (SSN) that I need to turn into text, so I want a macro that will put an apostrophe and a zero at the beginning of the numbers. I know that there are other ways to change a number to text (formatting, find/replace to add the apostrophe) but for what we are doing, manually editing to add the apostrophe and zero is the only thing that works. SO.. to my question. When I make the macro to do this, of course it simply adds the apostrophe and zero and repeats the number from the first cell over and over again. How can I make it select each cell's new data, move to the beginning of the number and add the apostrophe? Here is my macro that is obviously not working. ActiveCell.FormulaR1C1 = "'012365564" ActiveCell.Offset(1, 0).Range("A1").Select I see that the problem is that it is picking up the literal from the first line, but how can I make the macro edit the new numbers in each line and not repeat this text over and over. |
|
#6
|
|||
|
|||
|
I have the same issue - did you ever figure it out? If so, I would love to
know how you did it! "kayabob" wrote: I have a series of numbers (SSN) that I need to turn into text, so I want a macro that will put an apostrophe and a zero at the beginning of the numbers. I know that there are other ways to change a number to text (formatting, find/replace to add the apostrophe) but for what we are doing, manually editing to add the apostrophe and zero is the only thing that works. SO.. to my question. When I make the macro to do this, of course it simply adds the apostrophe and zero and repeats the number from the first cell over and over again. How can I make it select each cell's new data, move to the beginning of the number and add the apostrophe? Here is my macro that is obviously not working. ActiveCell.FormulaR1C1 = "'012365564" ActiveCell.Offset(1, 0).Range("A1").Select I see that the problem is that it is picking up the literal from the first line, but how can I make the macro edit the new numbers in each line and not repeat this text over and over. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Relative Cell position NOT working with or without macro | Excel Discussion (Misc queries) | |||
| Text wider than one cell is not displayed in the next empty cell | Excel Discussion (Misc queries) | |||
| Insert value of a cell as a filename | Excel Worksheet Functions | |||
| Macro or Function to make text size to suite text Length? | Excel Discussion (Misc queries) | |||
| Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions | |||
Hybrid Mode
