Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,885
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,885
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Problem incrementing rows

That did it Bob, thanks for your help. :

--
Message posted from http://www.ExcelForum.com

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,885
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Incrementing Rows and Columns Intern Excel Discussion (Misc queries) 1 June 5th 09 05:59 PM
Incrementing numbers problem AJ Excel Worksheet Functions 1 May 23rd 09 03:25 AM
fill down formula incrementing by every 12 rows westy Excel Discussion (Misc queries) 2 September 5th 07 04:54 AM
Incrementing formula by two rows Ben Hur Excel Worksheet Functions 1 February 24th 05 09:21 PM
Automatically 'incrementing' formulas for new rows. MediaScribe New Users to Excel 3 February 21st 05 06:29 PM


All times are GMT +1. The time now is 03:35 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"