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.
0
kayabob (10)
6/22/2005 3:44:06 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
359 Views

Similar Articles

[PageSpeed] 6

    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" <kayabob@discussions.microsoft.com> wrote in message
news:EF4C57CC-FCF2-4AE0-9607-E46665E0957F@microsoft.com...
> 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.


0
phillips1 (803)
6/22/2005 3:56:31 PM
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" <kayabob@discussions.microsoft.com> wrote in message
> news:EF4C57CC-FCF2-4AE0-9607-E46665E0957F@microsoft.com...
> > 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.
> 
> 
> 
0
kayabob (10)
6/22/2005 4:36:26 PM
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" <kayabob@discussions.microsoft.com> wrote in message
> news:EF4C57CC-FCF2-4AE0-9607-E46665E0957F@microsoft.com...
> > 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.
> 
> 
> 
0
GarysStudent (1572)
6/22/2005 4:36:32 PM
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.
0
Ginny007 (1)
6/22/2005 4:36:32 PM
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" <kayabob@discussions.microsoft.com> wrote in message
news:CFB6E395-3182-4DA1-ACCD-759E60E4D5D2@microsoft.com...
> 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" <kayabob@discussions.microsoft.com> wrote in message
> > news:EF4C57CC-FCF2-4AE0-9607-E46665E0957F@microsoft.com...
> > > 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.
> >
> >
> >


0
phillips1 (803)
6/22/2005 4:43:56 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Can't insert Char(1) into SQL Server from Stored Procedure
Hello I am trying to insert a char(1) field into a table from an ASP.Net/C# application. At first I was inserting rows into the table but none of the columns that I was inserting the rows into were char(1) columns, they were mostly int and varchar columns. Then I found out that there was one column in the list that was not supposed to be an int column but instead it was supposed to be one of the char(1) columns into the table. So I replaced the int column in the list with the char(1) column and since the value of the char(1) column resulted from a CheckBox ('Y...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

how to make macro work even a sheet is hidden.
hello there I have macro assigned to a button in Sheet 1 which goes to sheet 2. Press a button to Refresh and then PRINT preview It does work in normal state. I do not want the user to see the Sheet 2. I hid sheets from Format-sheet-hide. but the macro is not working when sheet 2 is hidden. how to overcome this error "can't execute code in break mode" the macro code is Sub Print_Preview() Sheets("PaySlips2009-10").Select Application.Run "'Latest 2009Payslip.xls'!Sheet2.HURows" ActiveWindow.SelectedSheets.PrintPreview ...

parameter for text
In Access 2003. In a form I have a parameter to search for a word at a a time Like [Froms].[Form].txtWords]&"*" which works well. .. In order to enter more than two oarameter separated by commas in txtwords I have been tryin the Str function like this "Instr([txtWords])" "Instr[txtWords]" The query displays no records at all. Could you suggest me how to do it Many thanks I would suggest separating by space and using this -- Like "*" & [Froms].[Form].txtWords] &"*" Remember that when you type in 'bell...

Shortcut for inserting names?
Hi, I am building many formulas, each with many named cells as part of it. Currently I have to go to Insert | Name | Paste with the mouse each time I want to insert a name. I looked in Walkenbach's Excel Bible but couldn't find any mention of a keyboard shortcut or other shortcut. Is there one I could take advantage of? I've got Excel 2002. Thanks, Jamie ...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Adding a Macro to a VLookup Function
In Excel 2000 -- I would like to create a summary spreadsheet (sheet2). For each time that "Name" appears in sheet 1, row *, take information from the same row, but columns 2 and 6 and bring it over to sheet 2 in the designated area. I know that you need to do a "VLookup" function and I got that to work. I know that you also need a "Loop" statement, so that it will continue to perform the Vlookup and bring over the information for each time that the name appears. I don't know how to write the formula so that the VLookup and the Loop are combined. Examp...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

Creating a Macro to Delete Commas #2
I have an excel file that the size will varry. I need a macro that will check all the fields for a comma. If there is one I would like to get rid of it. Does anyone have any idea how to do this? I have no idea and I have been assigned this task. Help --- Message posted from http://www.ExcelForum.com/ No macro required. ctrl-H for find/replace. find , replace nothing (leave the replace field blank). You can of course record that within a macro if you wish. Drabbacs >-----Original Message----- >I have an excel file that the size will varry. I need a macro that will >check ...

macros entering data
How do I create a macro that goes to one cell then waits until I enter new data, then goes to another cell and waits until I enter new data etc? thanks How about something like sub Enter_Data() dim NewValue NewValue = inputbox("Enter the value for cell A1: ") range("a1").value = NewValue NewValue = inputbox("Enter the value for cell G2: ") range("g2").value = NewValue NewValue = inputbox("Enter the value for cell I8: ") range("i8").value = NewValue end sub ...

How do I bind a XAML text box control to a dataset?
Hello; I am new to using WinFx and I am having trouble figuring out how to bind a text box to a field in a dataset. I found an MSDN article: http://msdn2.microsoft.com/en-us/library/ms752057.aspx My question deals with the text box code: <TextBlock Text="{Binding Path=ISBN}" Grid.Column="1" /> How does this text box know to bind to the field "ISBN" in "myDataSet"? How does the control implement the dataset? Thank you Monty ...

Macro to change default setting on startup
I am in need of a macro that can change a default setting in excel and for it to run on startup The task is: Tools Options General Web Options Files uncheck Update links on save Below is the recording of the macro: With ActiveWorkbook.WebOptions ..RelyOnCSS = True ..OrganizeInFolder = True ..UseLongFileNames = True ..DownloadComponents = False ..RelyOnVML = False ..AllowPNG = False ..ScreenSize = msoScreenSize800x600 ..PixelsPerInch = 96 ..Encoding = msoEncodingWestern End With With Application.DefaultWebOptions ..SaveHiddenData = True ..LoadPictures = True ....

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Need macro help to close excel
I have created a button in Access2000 that opens an Excel Spreadsheet. What I need now is assit in closing excel upon completion. I can get an excel macro to save my file and close the worksheet, but it is not closing excel entirely. I'm on project with this employer and could use a response today to fix this before I leave. Thanks much to any and all. My macro is as follows: Sub SaveClose() ' ' SaveClose Macro ' Macro recorded 9/27/2004 by cdjohnso ' ' Keyboard Shortcut: Ctrl+Shift+C ' ChDir "I:\SchoolsSurvey\Graphs_Reports" ActiveWorkb...

Excel Text Function (Right, Left)
My sheet has columns like this A 1. 63mmx 4kg 2. 75mmx 4kg SWR How can i detect that if a1:a2 is right,3 "swr", that it should be returned 3 otherwise 6. How can i add =right function with conditional formula. Help me Rao Ratan singh Hi =IF(RIGHT(A1,"SWR"),3,6) fill down your column (move cursor over bottom right hand corner of the cell when you see a +, double click) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Rao Ratan Singh" <RaoRatanSingh@discussions.microsoft.com> wrote in message news:D...