Insert cell data every given amount of rows

I got one column with data.
I want to insert a cell with text for a certain amount of rows in that 
column.
How can I do that?

Example with text for each 4 rows:

From
1
2
3
4
5
6
7
8
9

to
1
2
3
4
TEXT
5
6
7
8
TEXT
9


Regards
Mike 


0
mm2792 (7)
11/4/2005 4:51:55 PM
excel 39879 articles. 2 followers. Follow

3 Replies
178 Views

Similar Articles

[PageSpeed] 7

Try the following:

Dim RowNdx As Long
RowNdx = 4
Do Until Cells(RowNdx, "A").Value = ""
    Cells(RowNdx, "A").Insert
    Cells(RowNdx, "A").Value = "Text"
    RowNdx = RowNdx + 5
Loop



-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Mike Mike" <mm@mitechhostREMOVE.com> wrote in message 
news:LkMaf.233803$p_1.93541@tornado.tampabay.rr.com...
>I got one column with data.
> I want to insert a cell with text for a certain amount of rows 
> in that column.
> How can I do that?
>
> Example with text for each 4 rows:
>
> From
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
>
> to
> 1
> 2
> 3
> 4
> TEXT
> 5
> 6
> 7
> 8
> TEXT
> 9
>
>
> Regards
> Mike
> 


0
chip1 (1821)
11/4/2005 5:04:25 PM
Typo:

Change
RowNdx = 4
to
RowNdx = 5


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Chip Pearson" <chip@cpearson.com> wrote in message 
news:%23aP8QHW4FHA.2196@tk2msftngp13.phx.gbl...
> Try the following:
>
> Dim RowNdx As Long
> RowNdx = 4
> Do Until Cells(RowNdx, "A").Value = ""
>    Cells(RowNdx, "A").Insert
>    Cells(RowNdx, "A").Value = "Text"
>    RowNdx = RowNdx + 5
> Loop
>
>
>
> -- 
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Mike Mike" <mm@mitechhostREMOVE.com> wrote in message 
> news:LkMaf.233803$p_1.93541@tornado.tampabay.rr.com...
>>I got one column with data.
>> I want to insert a cell with text for a certain amount of rows 
>> in that column.
>> How can I do that?
>>
>> Example with text for each 4 rows:
>>
>> From
>> 1
>> 2
>> 3
>> 4
>> 5
>> 6
>> 7
>> 8
>> 9
>>
>> to
>> 1
>> 2
>> 3
>> 4
>> TEXT
>> 5
>> 6
>> 7
>> 8
>> TEXT
>> 9
>>
>>
>> Regards
>> Mike
>>
>
> 


0
chip1 (1821)
11/4/2005 5:08:32 PM
It worked.
Thank you!!

"Chip Pearson" <chip@cpearson.com> wrote in message 
news:uQIAkJW4FHA.3636@TK2MSFTNGP09.phx.gbl...
> Typo:
>
> Change
> RowNdx = 4
> to
> RowNdx = 5
>
>
> -- 
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
>
> "Chip Pearson" <chip@cpearson.com> wrote in message 
> news:%23aP8QHW4FHA.2196@tk2msftngp13.phx.gbl...
>> Try the following:
>>
>> Dim RowNdx As Long
>> RowNdx = 4
>> Do Until Cells(RowNdx, "A").Value = ""
>>    Cells(RowNdx, "A").Insert
>>    Cells(RowNdx, "A").Value = "Text"
>>    RowNdx = RowNdx + 5
>> Loop
>>
>>
>>
>> -- 
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "Mike Mike" <mm@mitechhostREMOVE.com> wrote in message 
>> news:LkMaf.233803$p_1.93541@tornado.tampabay.rr.com...
>>>I got one column with data.
>>> I want to insert a cell with text for a certain amount of rows in that 
>>> column.
>>> How can I do that?
>>>
>>> Example with text for each 4 rows:
>>>
>>> From
>>> 1
>>> 2
>>> 3
>>> 4
>>> 5
>>> 6
>>> 7
>>> 8
>>> 9
>>>
>>> to
>>> 1
>>> 2
>>> 3
>>> 4
>>> TEXT
>>> 5
>>> 6
>>> 7
>>> 8
>>> TEXT
>>> 9
>>>
>>>
>>> Regards
>>> Mike
>>>
>>
>>
>
> 


0
mm2792 (7)
11/4/2005 5:40:20 PM
Reply:

Similar Artilces:

Unprotected cells after a download
I've got a simple small spreadsheet that contains a lot of protected cells where the user can't put the cursor, and there is also some conditional formatting. I've put this file on a web page to be downloaded. When it's downloaded if people click open, instead of "save to disk" the the protection appears to go off and the user can put the cursor anywhere (though the passwords and all are still in there), and if the file is then saved the conditional formatting changes. What's up? The two changes completely change the way the file works. I had somebody else ...

How can I link a cell to an external photo?
I have a list of employees listing information about each and would like to show photo ID from link to get a photo popup to identify each one by their picture check out hyperlinks.... -- paul remove nospam for email addy! "graphicsjunky" wrote: > I have a list of employees listing information about each and would like to > show photo ID from link to get a photo popup to identify each one by their > picture Hi, you can also insert a photo into a comment. This will show a photo when hovering over the cell if that's what yo are after. Insert a comment into the...

if is error then "No Data", if not then average range
I believe the formula should be something like: iferror(average(C1:F1=TRUE,"No Data), average(C1:F1) =IFERROR(AVERAGE(C1:F1),"No Data") "SK08" wrote: > I believe the formula should be something like: > iferror(average(C1:F1=TRUE,"No Data), average(C1:F1) > . > ...

Where do I get the info for cell phone email
got a new cell phone ..need to know where to get info for my email account Likeif its a Provider,username,,return address,server type,POP3 port,SMTPserver.ect...where do I get all this info on outlook? thanks Not sure if your cell phone has a POP3 mailbox associated with it. You might want to check with your cell provider. If you mean that you want to get your info for your current email account to put into your cellphone you should be able to find most of it here in Outlook: Tools menu > E-mail accounts "josh" <josh@discussions.microsoft.com> wrote in message news:...

how do i create a macro that would check the contents of cells
Hi, I am stcuk on Excel VB. I am trying to create a macro that when run would check to see if there is an 'X' in 6 specific cells on a row(the cells are next to each other), there is an X in all of them it would then display a msg saying 'not possible', else it would not doing anything. I hope i did pass the problem across in a sensible manner. Thanks for helping. I'd just use another cell next to those 6 cells: =if(countif(a1:f1,"x")=6,"Not Possible","") Format it in a nice big red font. Jade wrote: > > Hi, I am stcuk on Excel V...

Same Code, Different Cells
I have just coded a user interface in the guise of a few forms for a spreadsheet I've created. The spreadsheet has the same table duplicated 27 times all the way down it but I have only coded the forms (user interface) to work for the first table on my spreadsheet and it took ages to give instructions for each cell etc. Is there anyway I can duplicate my code for the rest of my tables (in the same sheet) easily? ie can I duplicate the code but change the cells that are referred to in that code to suit the other tables? I know I can copy and paste but I'd still have to go through it a...

choosing cell to top left of visible window
Excel 2003 and 2007 > Because displays vary, when I click a macro button to go to a different part of the sheet, I want to choose a cell in column A so that it appears at the upper left of the current window. I can't seem to get it to work in Excel 2003 - the selected cell appears some where in the middle. maybe this will work for you. type this in the immediate window ?cells(Activewindow.Scrollrow,activewindow.ScrollColumn).address -- Gary Keramidas Excel 2003 "AZSteve" <AZSteve@discussions.microsoft.com> wrote in message news:A113341...

Grabbing info from every 6 rows
I would like to have cell B1 equal the contents of A1, B2 = A7, B3 = A13 and so on. Basically, I'm trying to grab the values from every 6 cells in column A. Does anyone know how to do this? I was hoping I could do something like B1 = A'I1', and then copy and past this to all the subsequent b cells. I1 would equal 1, I2 would equal 6, I3 would equal 13 and so on. =INDEX(A:A,ROW()*6-5) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mcolson" <mcolson1590@gmail.com> wrote in message news:1179926982.777535.28...

most recent rows
I have a table where the users are supposed to input a row each month for each account. I need some way to audit the table to make sure I have an entry for each month in the current 12 months. The table as a field called BillDate but if it's an account that bills near the end/beginning of the month you might have 2 BillDates in the same month. For example 3/1/08 is the Februay BillDate and 3/30/08 is the March BillDate. So I added a field called BillMoYr but I'm not sure how to write the query to get the current 12 rows for each account. In the example below I would like to ...

Number to Number "Data Type Mismatch in Expression" in Join
I am attempting to write a query joining two linked tables in a query by payroll number (all numeric). When write the query and join by payroll number I get the "Data Type Mismatch in Expression" error. Both tables have the Data Type as "Number", but the only difference I can tell is one has "Field Size" as "Decimal" and the other has "Field Size" as "Double". I have even tried to join on the VAL() of each payroll number field with no luck. Any ideas? "Decimal" and "Double" are actually different dat...

Showing negative 'Times' in cells.
Please help! How can I display time when it goes negative in the cells? My current Format setting is as follows: [Blue][h]:mm;[Red]-[h]:mm Any help would be most appreciated. Thanks! The (WinXL) default 1900 date system can't show negative time formats. One way is to use the 1904 system: Tools/Options/Calculation, check the 1904 Date system checkbox Then use your custom format In article <4ad1a266a230cc321f36c104f8ec81f9@localhost.talkaboutsoftware.com>, "Chives" <cheekychives@yahoo.co.uk> wrote: > Please help! > > How can I display time wh...

Locking data
Hi, Is there any way that once a user has clicked a button or entered data into a cell it is locked in an equivalent cell? For example in Cell B1 formula =A1 in Cell A1 = 5 If the 5 becomes 6 then so will B1. But is there anyway, that by using a formula or short onClick code I can make it so that B1 remains 5 even if A1 changes. Thanks Antony Right click on the worksheet tab that should exhibit this behavior and select view code. Paste this in: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Targe...

VBA code to automatically colour cells depending on text?
Hi, - with the skill of Don Guillet (Excel MVP - thank you!!) I have nearly finished a ‘to-do’ list to help organise my life as an infant school teacher. see attached link: http://www.box.net/shared/ejzn141dux The worksheet works like this: Enter desired text in cell ‘c3, h3 or m3’ and then use the drop down button in ‘b3, g3 or l3’ to determine the position where the text is to be placed in the below list. If there is text already in that position, all the text below is moved down one cell. If you delete text from a cell then the text in the cells below moves up one. ...

number insert
any help on this would be appreciated , i am trying to insert a 20 digit number into a work sheet, but it does not appear as i want it example: 74540182653438069674 is what i want but what appears is 7.45402E+19 also in the bar under the tools (sorry not sure what it is called) the number there changes to 74540182653438000000 thanks in advance for any help JKF Hi, Are your Cells formatted as Number? You can check this by Selecting Format>Cells>Number, then select Number form the list. Hope this helps, Gav. "jkf" wrote: > any help on this would be ...

hidden rows & columns slow file open
Hi, I have found that if I have extensive Rows and Columns Hidden in the Sheets of a large Workbook, that it causes Excel 2000 to take a long time to open a file. It opens quickly in version 2003. If I set all Columns and Rows to Hidden = False during the BeforeSave event in ThisWorkbook sheet, the file re-opens quickly. Note: During use of the workbook I have individual Worksheet_Active event procedures that set certain Rows and Columns to Hidden = True based on various criteria. Is this a known issue, and the only solution? It makes saving the file slow... not as slow as opening, bu...

Group And Sum Data
I have 2 columns, *hrs* and *fitter*, on *Sheet1*. I need, on *Sheet2*, something that will group all the fitters by name order and add up their total hrs for each and list them - Any ideas on this? Sheet1 Code: -------------------- Hrs Fitter 3.00 Brian Franklin 3.00 Chris Leader 1.50 Nick Ryan 1.50 Chris Leader 7.00 Chris Leader 3.00 Chris Leader 7.25 Nick Ryan 7.00 Nick Ryan -------------------- Sheet2 Code: -------------------- Hrs Fitter 3.00 Brian Franklin 14.50 Chris Leader 15.75 Nick Ryan -------------------- Cheers Ian -- iwgunter -----------...

Graphing only even/odd numbered cells
Is it possible to graph only the even (or odd) cells. For example- I have information only in A2, A4, A6, A8 etc. but cells A3, A5, A7, A9 are NULL. Therefore, instead of manually dragging the information from the even numbered cells and forming a continuous column, is it possible to form a graph of jsut the even numbered cells of that column? Thanks, -- gantzlia ------------------------------------------------------------------------ gantzlia's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30067 View this thread: http://www.excelforum.com/showthread.php?threa...

Count Data in Series
Hi, I'm trying to write a formula that will go down a column and count the number of one value cells that occur between those of another value. For example, given: P S S P S S S S P P S P in column A, I'd like a formula in column B that gives: P S S P 2 S S S S P 4 P 0 S P 1 I want the columns opposite the "S" to remain empty. I've been playing with the =FREQUENCY function, but haven't been able to figure it out. Thanks, Demosthenes wrote: > Hi, > > I'm trying to write a formula that will...

Excluding email data
I want to make a query that will show me only parts of the email addresses in a specific field. So, if I have an email like john.smith@hotmail.com, and I only want the query to show me "hotmail.com" for that record, how do I do this? Basically, I ask b/c I am trying to group the different types of email accounts that our clients have. -- Cloudbuster On Mon, 28 Dec 2009 16:39:08 -0800, Cloudbuster <airwitte@hotmail.com.(donotspam)> wrote: >I want to make a query that will show me only parts of the email addresses in >a specific field. So, if I have an em...

more than one lines in same cell
How can I type more than one lines in a cell? Thanks, NYBo -- NYBo ----------------------------------------------------------------------- NYBoy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=836 View this thread: http://www.excelforum.com/showthread.php?threadid=27505 alt enter at the end of each line -- Don Guillett SalesAid Software donaldb@281.com "NYBoy" <NYBoy.1f5yzb@excelforum-nospam.com> wrote in message news:NYBoy.1f5yzb@excelforum-nospam.com... > > How can I type more than one lines in a cell? > > Thanks, > NYBoy &...

Delete all Rows containing #N/A once a VLookup has been performed
Hi I am recording a Macro which uses a VLookup to fill in values in column A from another file. Where a match is not found it displays #N/A. I can use AutoFilter to filter out all the #N/A's and then delete these rows. The problem is that the orginal file is downloaded on a regular basis and the number of rows varies and therefore the number of #N/A's. Is there macro code which will automatically delete all rows containing #N/A's. Thanks Shirley -- Shirley Munro ------------------------------------------------------------------------ Shirley Munro's Profile: http://ww...

Data file check in progress
Office OL 2007 Win XP Pro SP2 Apparently, Outlook "closed improperly" the other day. I can't remember exactly why. When I restarted OL, I got the "usual" alert that it had closed improperly, that the file would undergo whatever check occurs when that happens, and that the system performance may be affected during the check. The lower righthand of the screen showed an icon with two gears, and hovering the mouse over the icon produced the tip "Data file in progress." This went on for several minutes and then it disappeared. Now, on restarting OL again, it&#...

Resizing chart because of refreshing Chart Data through a query
When I refresh data in a report, I always have to go back and adjust my graphs. For example, the data label will to be out of the chart area or I have to resize a pie chart for the new refresh. Is this just a short coming in the software? Do you know of any add-on product that would help with this problem? Thanks ...

Making a Pivot chart from 3 differnt sets of data / worksheets.
Hi, I have 2 workshsheets of data with a list of delivery dates of about 10 different products over the last 3 years, (i cant combine these to just 1 worksheet for other separate reasons) and a 3rd worksheet with a column of the same products and dates that some of these products became faulty and were sent back to me . On a 4th worksheet, I am trying to do a graph that looks at the 3 worksheets and plots pivot table of dates and amount of each product deliveries & returned to me. So as i select ,say, product A from that pivot list, it produces a graph for these 3 variables (dat...

error when inserting into taSopHdrIvcInsert
Has anbody seen the below error ? "Sql procedure error codes returned:\r\nError Number = 68 Stored Procedure taSopHdrIvcInsert Error Description = Document ID is invalid\r\nNode Identifier Parameters: taSopHdrIvcInsert \r\nSOPNUMBE = INV04\r\nSOPTYPE = 3\r\nRelated Error Code Parameters for Node : taSopHdrIvcInsert \r\nDOCID = INV2004\r\nSOPTYPE = 3\r\n\r\n\r\n<taSopHdrIvcInsert><SOPTYPE>3</SOPTYPE><DOCID>INV2004</DOCID><SOPNUMBE>INV04</SOPNUMBE><LOCNCODE>WAREHOUSE<...