Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




Problem incrementing rows
This code almost does what I need, but I'm stuck.
When I run this each cell that matches increments by two. I want to keep the same value for the whole row. Then drop a row and increment by two on the next row. Example: (Do not want) Pack112 Pack114 Pack116 (Do want) Pack112 Pack112 Pack112 Pack114 Pack114 Pack114 i = 112 For Each CELL In Range("A1:DD267") If CELL.Value = "PACK" Then i = i + 2 CELL.Value = "PACK" & CStr(i) If i 140 Then i = 0 End If Next CELL Thanks  Message posted from http://www.ExcelForum.com/ 
#2
Posted to microsoft.public.excel.programming




Problem incrementing rows
Hi
a little bit kludgy but give it a try Sub foo() Dim i Dim row_count Dim cell Dim rng i = 112 Set rng = Range("A1:DD267") row_count = rng.Row For Each cell In Range("A1:DD267") If row_count < cell.Row Then row_count = cell.Row i = i + 2 End If If cell.Value = "PACK" Then cell.Value = "PACK" & CStr(i) End If If i 140 Then i = 0 End If Next cell End Sub  Regards Frank Kabel Frankfurt, Germany This code almost does what I need, but I'm stuck. When I run this each cell that matches increments by two. I want to keep the same value for the whole row. Then drop a row and increment by two on the next row. Example: (Do not want) Pack112 Pack114 Pack116 (Do want) Pack112 Pack112 Pack112 Pack114 Pack114 Pack114 i = 112 For Each CELL In Range("A1:DD267") If CELL.Value = "PACK" Then i = i + 2 CELL.Value = "PACK" & CStr(i) If i 140 Then i = 0 End If Next CELL Thanks confused:  Message posted from http://www.ExcelForum.com/ 
#3
Posted to microsoft.public.excel.programming




Problem incrementing rows
Confused,
How about this i = 112 For Each row In Rows("1:267") For Each cell In row.Columns("A:D") If cell.Value = "PACK" Then cell.Value = "PACK" & CStr(i) End If Next cell i = i + 2 If i 140 Then i = 0 Next row  HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "hotherps " wrote in message ... This code almost does what I need, but I'm stuck. When I run this each cell that matches increments by two. I want to keep the same value for the whole row. Then drop a row and increment by two on the next row. Example: (Do not want) Pack112 Pack114 Pack116 (Do want) Pack112 Pack112 Pack112 Pack114 Pack114 Pack114 i = 112 For Each CELL In Range("A1:DD267") If CELL.Value = "PACK" Then i = i + 2 CELL.Value = "PACK" & CStr(i) If i 140 Then i = 0 End If Next CELL Thanks  Message posted from http://www.ExcelForum.com/ 
#4
Posted to microsoft.public.excel.programming




Problem incrementing rows
Thanks for the help Frank, I'm almost there. It is incrementing properl
but it is going backwards? It begins counting at 136 and if there are any rows without values it is incrementing the rows a if the values were there PACK112 PACK112 PACK112 PACK112 PACK112 PACK112 PACK114 PACK114 PACK114 PACK114 PACK114 PACK114 PACK116 PACK116 PACK116 PACK116 PACK116 PACK118 PACK118 PACK118 PACK118 empty row empty row empty row PACK128 PACK128 PACK128 PACK128 PACK128 PACK130 PACK130 PACK130 PACK130 Is there a way to make it only count on rows that have data  Message posted from http://www.ExcelForum.com 
#5
Posted to microsoft.public.excel.programming




Problem incrementing rows
i = 112
For Each row In Rows("1:267") cn t = 0 For Each cell In row.Columns("A:D") If cell.Value = "PACK" Then cell.Value = "PACK" & CStr(i) cnt = cnt 1 End If Next cell If cnt 0 Then i = i + 2 If i 140 Then i = 0 End If Next row  HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "hotherps " wrote in message ... Thanks for the help Frank, I'm almost there. It is incrementing properly but it is going backwards? It begins counting at 136 and if there are any rows without values it is incrementing the rows as if the values were there PACK112 PACK112 PACK112 PACK112 PACK112 PACK112 PACK114 PACK114 PACK114 PACK114 PACK114 PACK114 PACK116 PACK116 PACK116 PACK116 PACK116 PACK118 PACK118 PACK118 PACK118 empty row empty row empty row PACK128 PACK128 PACK128 PACK128 PACK128 PACK130 PACK130 PACK130 PACK130 Is there a way to make it only count on rows that have data?  Message posted from http://www.ExcelForum.com/ 
#6
Posted to microsoft.public.excel.programming




Problem incrementing rows
Hi
in my test it started with 112?. Does your data start in row 1 or a different one? For the second part to skip empty rows I adapted Bob's solutions (as it was IMHO the better one): Sub foo3() Dim i Dim row As Range Dim cell As Range Dim row_entry As Boolean row_entry = False i = 112 For Each row In Rows("1:267") For Each cell In row.Columns("A:D") If cell.Value = "PACK" Then cell.Value = "PACK" & CStr(i) If Not row_entry Then row_entry = True End If Next cell If row_entry Then i = i + 2 If i 140 Then i = 0 row_entry = False End If Next row End Sub  Regards Frank Kabel Frankfurt, Germany Thanks for the help Frank, I'm almost there. It is incrementing properly but it is going backwards? It begins counting at 136 and if there are any rows without values it is incrementing the rows as if the values were there PACK112 PACK112 PACK112 PACK112 PACK112 PACK112 PACK114 PACK114 PACK114 PACK114 PACK114 PACK114 PACK116 PACK116 PACK116 PACK116 PACK116 PACK118 PACK118 PACK118 PACK118 empty row empty row empty row PACK128 PACK128 PACK128 PACK128 PACK128 PACK130 PACK130 PACK130 PACK130 Is there a way to make it only count on rows that have data?  Message posted from http://www.ExcelForum.com/ 
#7
Posted to microsoft.public.excel.programming




Problem incrementing rows

#8
Posted to microsoft.public.excel.programming




Problem incrementing rows
"Frank Kabel" wrote in message
... For the second part to skip empty rows I adapted Bob's solutions (as it was IMHO the better one): and mine!<vbg 
#9
Posted to microsoft.public.excel.programming




Problem incrementing rows
For the second part to skip empty rows I adapted Bob's solutions (as it was IMHO the better one): and mine!<vbg Bowing in adminration <VVBG 
#10
Posted to microsoft.public.excel.programming




Problem incrementing rows
Thanks Bob and Frank! it is working like it should, but I am going to
post a new question and I would like your feedback if possible. Thanks again  Message posted from http://www.ExcelForum.com/ 
#11
Posted to microsoft.public.excel.programming




Problem incrementing rows
Forgotten about this one. If it's a new subject, start a new thread, this
one is old and tired now<G  HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "hotherps " wrote in message ... Thanks Bob and Frank! it is working like it should, but I am going to post a new question and I would like your feedback if possible. Thanks again  Message posted from http://www.ExcelForum.com/ 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Incrementing Rows and Columns  Excel Discussion (Misc queries)  
Incrementing numbers problem  Excel Worksheet Functions  
fill down formula incrementing by every 12 rows  Excel Discussion (Misc queries)  
Incrementing formula by two rows  Excel Worksheet Functions  
Automatically 'incrementing' formulas for new rows.  New Users to Excel 