I think what I am trying to do is not that difficult, but I just can't
get it. I have a spreadsheet with a range of H:10 to DD:267 the work
"Pack" appears it is an assignment. I amtry to number each occurence of
"Pack" i.e. Pack1, Pack2 up to Pack41. When the code finds the word I
want it to fill in the next 8 cells to the right with the name Pack and
the incremented number. It should loop through all of the rows
repeating the same pattern until it reachs Pack41, it should then start
over at Pack1 and continue to loop to the end of the range.

Is this very complicated? I'm trying for...next, do until... nothing
works

Thanks

Attachment filename: numberpack.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=411038
---
Message posted from http://www.ExcelForum.com/

Untested.

i = 0
For Each cell In Range("H10:DD2567")
If cell.Value = "Pack" Then
i = i+1
For j = 1 To 8
cell.Offset(0,j).Value = "Pack" & CStr(i)
Next j
IF i 40 i = 0
End If
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hotherps " wrote in message
...
I think what I am trying to do is not that difficult, but I just can't
get it. I have a spreadsheet with a range of H:10 to DD:267 the work
"Pack" appears it is an assignment. I amtry to number each occurence of
"Pack" i.e. Pack1, Pack2 up to Pack41. When the code finds the word I
want it to fill in the next 8 cells to the right with the name Pack and
the incremented number. It should loop through all of the rows
repeating the same pattern until it reachs Pack41, it should then start
over at Pack1 and continue to loop to the end of the range.

Is this very complicated? I'm trying for...next, do until... nothing
works

Thanks

Attachment filename: numberpack.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=411038
---
Message posted from http://www.ExcelForum.com/

That worked great Bob! I'm really going to have to look at it to try an
figure out exactly what is happening.
Let me ask one more question. Let's say that I do not want the sam
Pack number in any given column. i.e. Col F, Row 5 = Pack1 I do no
want any more cells in Col F to be pack Pack1 instead Increment th
number

I'm thinking an If statement somewhere in there would do it? But how t
phrase it?


Bob Phillips wrote:
*Untested.

i = 0
For Each cell In Range("H10:DD2567")
If cell.Value = "Pack" Then
i = i+1
For j = 1 To 8
cell.Offset(0,j).Value = "Pack" & CStr(i)
Next j
IF i 40 i = 0
End If
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hotherps " wrote i
message
...
I think what I am trying to do is not that difficult, but I jus
can't
get it. I have a spreadsheet with a range of H:10 to DD:267 th
work
"Pack" appears it is an assignment. I amtry to number eac
occurence of
"Pack" i.e. Pack1, Pack2 up to Pack41. When the code finds the wor
I
want it to fill in the next 8 cells to the right with the name Pac
and
the incremented number. It should loop through all of the rows
repeating the same pattern until it reachs Pack41, it should the
start
over at Pack1 and continue to loop to the end of the range.

Is this very complicated? I'm trying for...next, do until..
nothing
works

Thanks

Attachment filename: numberpack.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=411038
---
Message posted from http://www.ExcelForum.com/
* :

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

I think this is what you are asking

i = 0
For Each cell In Range("H10:DD2567")
If cell.Value = "Pack" Then
i = i+1
For j = 1 To 8
If cell.Offset(0,j).Address < "F5" Then
cell.Offset(0,j).Value = "Pack" & CStr(i)
End If
Next j
IF i 40 i = 0
End If
Next cell


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hotherps " wrote in message
...
That worked great Bob! I'm really going to have to look at it to try and
figure out exactly what is happening.
Let me ask one more question. Let's say that I do not want the same
Pack number in any given column. i.e. Col F, Row 5 = Pack1 I do not
want any more cells in Col F to be pack Pack1 instead Increment the
number

I'm thinking an If statement somewhere in there would do it? But how to
phrase it?


Bob Phillips wrote:
*Untested.

i = 0
For Each cell In Range("H10:DD2567")
If cell.Value = "Pack" Then
i = i+1
For j = 1 To 8
cell.Offset(0,j).Value = "Pack" & CStr(i)
Next j
IF i 40 i = 0
End If
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hotherps " wrote in
message
...
I think what I am trying to do is not that difficult, but I just
can't
get it. I have a spreadsheet with a range of H:10 to DD:267 the
work
"Pack" appears it is an assignment. I amtry to number each
occurence of
"Pack" i.e. Pack1, Pack2 up to Pack41. When the code finds the word
I
want it to fill in the next 8 cells to the right with the name Pack
and
the incremented number. It should loop through all of the rows
repeating the same pattern until it reachs Pack41, it should then
start
over at Pack1 and continue to loop to the end of the range.

Is this very complicated? I'm trying for...next, do until...
nothing
works

Thanks

Attachment filename: numberpack.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=411038
---
Message posted from http://www.ExcelForum.com/
* :D


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

so what should be placed there? The algorithm you provided was to number
sequentially - now you don't want to use that algorithm for some case, so
what algorithm do you want? Do you want to just skip that number - what
happens if that fails and you get to the point where skipping no longer
works?

--
Regards,
Tom Ogilvy

hotherps wrote in message
...
That worked great Bob! I'm really going to have to look at it to try and
figure out exactly what is happening.
Let me ask one more question. Let's say that I do not want the same
Pack number in any given column. i.e. Col F, Row 5 = Pack1 I do not
want any more cells in Col F to be pack Pack1 instead Increment the
number

I'm thinking an If statement somewhere in there would do it? But how to
phrase it?


Bob Phillips wrote:
*Untested.

i = 0
For Each cell In Range("H10:DD2567")
If cell.Value = "Pack" Then
i = i+1
For j = 1 To 8
cell.Offset(0,j).Value = "Pack" & CStr(i)
Next j
IF i 40 i = 0
End If
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hotherps " wrote in
message
...
I think what I am trying to do is not that difficult, but I just
can't
get it. I have a spreadsheet with a range of H:10 to DD:267 the
work
"Pack" appears it is an assignment. I amtry to number each
occurence of
"Pack" i.e. Pack1, Pack2 up to Pack41. When the code finds the word
I
want it to fill in the next 8 cells to the right with the name Pack
and
the incremented number. It should loop through all of the rows
repeating the same pattern until it reachs Pack41, it should then
start
over at Pack1 and continue to loop to the end of the range.

Is this very complicated? I'm trying for...next, do until...
nothing
works

Thanks

Attachment filename: numberpack.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=411038
---
Message posted from http://www.ExcelForum.com/
* :D


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

Bob, I tried that new code you typed, it really did not do anthing, i
just showed the hourglass for a couple of seconds. Should I hav
replaced the original code with that?












Bob Phillips wrote:
*I think this is what you are asking

i = 0
For Each cell In Range("H10:DD2567")
If cell.Value = "Pack" Then
i = i+1
For j = 1 To 8
If cell.Offset(0,j).Address < "F5" Then
cell.Offset(0,j).Value = "Pack" & CStr(i)
End If
Next j
IF i 40 i = 0
End If
Next cell


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hotherps " wrote i
message
...
That worked great Bob! I'm really going to have to look at it t
try and
figure out exactly what is happening.
Let me ask one more question. Let's say that I do not want th
same
Pack number in any given column. i.e. Col F, Row 5 = Pack1 I d
not
want any more cells in Col F to be pack Pack1 instead Incremen
the
number

I'm thinking an If statement somewhere in there would do it? Bu
how to
phrase it?


Bob Phillips wrote:
*Untested.

i = 0
For Each cell In Range("H10:DD2567")
If cell.Value = "Pack" Then
i = i+1
For j = 1 To 8
cell.Offset(0,j).Value = "Pack" & CStr(i)
Next j
IF i 40 i = 0
End If
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hotherps " wrote in
message
...
I think what I am trying to do is not that difficult, but
just
can't
get it. I have a spreadsheet with a range of H:10 to DD:26
the
work
"Pack" appears it is an assignment. I amtry to number each
occurence of
"Pack" i.e. Pack1, Pack2 up to Pack41. When the code finds th
word
I
want it to fill in the next 8 cells to the right with the nam
Pack
and
the incremented number. It should loop through all of the rows
repeating the same pattern until it reachs Pack41, it shoul
then
start
over at Pack1 and continue to loop to the end of the range.

Is this very complicated? I'm trying for...next, do until...
nothing
works

Thanks

Attachment filename: numberpack.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=411038
---
Message posted from http://www.ExcelForum.com/
* :D


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


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

Tom, you raise some good questions, I guess I never considered wha
would happen if it failed. Maybe the code should skip over the numbe
that is going to be repeated. The numbered part of the first peice o
code was to define a type and a location. I have other types that d
not require a location such as "LVD" I need to assign this task th
same way the previous code does, but the counter number should not b
visible.

There are also more codes, is this a place where you would want to us
a case statement? I'm a little fuzzy on how they work. Or is it just
matter of looping code that references all of the tasks until the en
of the range is hit.

Thans very much for your responsed Bob and Tom, I'm starting to get th
idea.

I'm including the excel sheet I am experimenting on if you would lik
to see it.










Tom Ogilvy wrote:
*so what should be placed there? The algorithm you provided was t
number
sequentially - now you don't want to use that algorithm for som
case, so
what algorithm do you want? Do you want to just skip that number
what
happens if that fails and you get to the point where skipping n
longer
works?

--
Regards,
Tom Ogilvy

hotherps wrote in message
...
That worked great Bob! I'm really going to have to look at it t
try and
figure out exactly what is happening.
Let me ask one more question. Let's say that I do not want th
same
Pack number in any given column. i.e. Col F, Row 5 = Pack1 I d
not
want any more cells in Col F to be pack Pack1 instead Incremen
the
number

I'm thinking an If statement somewhere in there would do it? Bu
how to
phrase it?


Bob Phillips wrote:
*Untested.

i = 0
For Each cell In Range("H10:DD2567")
If cell.Value = "Pack" Then
i = i+1
For j = 1 To 8
cell.Offset(0,j).Value = "Pack" & CStr(i)
Next j
IF i 40 i = 0
End If
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hotherps " wrote in
message
...
I think what I am trying to do is not that difficult, but
just
can't
get it. I have a spreadsheet with a range of H:10 to DD:26
the
work
"Pack" appears it is an assignment. I amtry to number each
occurence of
"Pack" i.e. Pack1, Pack2 up to Pack41. When the code finds th
word
I
want it to fill in the next 8 cells to the right with the nam
Pack
and
the incremented number. It should loop through all of the rows
repeating the same pattern until it reachs Pack41, it shoul
then
start
over at Pack1 and continue to loop to the end of the range.

Is this very complicated? I'm trying for...next, do until...
nothing
works

Thanks

Attachment filename: numberpack.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=411038
---
Message posted from http://www.ExcelForum.com/
* :D


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


Attachment filename: numberpack.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=41113
--
Message posted from http://www.ExcelForum.com

I don't think that is what he wants:

[given there is a Pack1 in column F at a previous row and the code would put
Pack1 in column F at this row then ]
. . . I do not want any more cells in Col F to be pack Pack1 instead
Increment the
number

Your revision just skips cell F5.

--
Regards,
Tom Ogilvy


Bob Phillips wrote in message
...
I think this is what you are asking

i = 0
For Each cell In Range("H10:DD2567")
If cell.Value = "Pack" Then
i = i+1
For j = 1 To 8
If cell.Offset(0,j).Address < "F5" Then
cell.Offset(0,j).Value = "Pack" & CStr(i)
End If
Next j
IF i 40 i = 0
End If
Next cell


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hotherps " wrote in message
...
That worked great Bob! I'm really going to have to look at it to try and
figure out exactly what is happening.
Let me ask one more question. Let's say that I do not want the same
Pack number in any given column. i.e. Col F, Row 5 = Pack1 I do not
want any more cells in Col F to be pack Pack1 instead Increment the
number

I'm thinking an If statement somewhere in there would do it? But how to
phrase it?


Bob Phillips wrote:
*Untested.

i = 0
For Each cell In Range("H10:DD2567")
If cell.Value = "Pack" Then
i = i+1
For j = 1 To 8
cell.Offset(0,j).Value = "Pack" & CStr(i)
Next j
IF i 40 i = 0
End If
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hotherps " wrote in
message
...
I think what I am trying to do is not that difficult, but I just
can't
get it. I have a spreadsheet with a range of H:10 to DD:267 the
work
"Pack" appears it is an assignment. I amtry to number each
occurence of
"Pack" i.e. Pack1, Pack2 up to Pack41. When the code finds the word
I
want it to fill in the next 8 cells to the right with the name Pack
and
the incremented number. It should loop through all of the rows
repeating the same pattern until it reachs Pack41, it should then
start
over at Pack1 and continue to loop to the end of the range.

Is this very complicated? I'm trying for...next, do until...
nothing
works

Thanks

Attachment filename: numberpack.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=411038
---
Message posted from http://www.ExcelForum.com/
* :D


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

Here is the code at the moment :Sub Button1_Click()
Dim i As Integer

i = 0
For Each cell In Range("A1:DD267")
If cell.Value = "PACK" Then
i = i + 1
For j = 0 To 7
cell.Offset(0, j).Value = "Pack" & CStr(i)
Next j
If i 40 Then i = 0
End If
Next cell

End Sub
Sub Button2_Click()
Dim i As Integer
Dim intcounter As Integer

intcounter = 0
For Each cell In Range("A1:DD267")
If cell.Value = "LVD" Then
intcounter = intcounter + 1
For j = 0 To 7
cell.Offset(0, j).Value = "LVD" & intcounter
Next j
If intcounter 8 Then intcounter = 0
End If
Next cell
End Sub


I'm using two command buttons two start the code. I'd like to be able
to consolidate these actions together. I also have other codes that I
would like the same result for. However I want to hide the counter on
every task other than "Pack"


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

Sub Button2_Click()
Dim i As Integer, k as integer, j as integer
Dim intcounter As Integer
Dim varr, varr1
Dim cell as Range
varr = Array("PACK","LVD")
varr1 = Array(40,8)
for k = lbound(varr) to ubound(varr)
intcounter = 0
For Each cell In Range("A1:DD267")
If cell.Value = varr(k) Then
intcounter = intcounter + 1
For j = 0 To 7
if varr(k) = "PACK" Then
cell.Offset(0, j).Value = "PACK" & intcounter
else
cell.offset(0,j).Value = varr(k)
end if
Next j
If intcounter varr(k) Then intcounter = 0
End If
Next cell
Next k
End Sub

But I am not sure what the point of using a count on items other than PACK
serves.

Add values to the two arrays (varr, varr1)

--
Regards,
Tom Ogilvy


hotherps wrote in message
...
Here is the code at the moment :Sub Button1_Click()
Dim i As Integer

i = 0
For Each cell In Range("A1:DD267")
If cell.Value = "PACK" Then
i = i + 1
For j = 0 To 7
cell.Offset(0, j).Value = "Pack" & CStr(i)
Next j
If i 40 Then i = 0
End If
Next cell

End Sub
Sub Button2_Click()
Dim i As Integer
Dim intcounter As Integer

intcounter = 0
For Each cell In Range("A1:DD267")
If cell.Value = "LVD" Then
intcounter = intcounter + 1
For j = 0 To 7
cell.Offset(0, j).Value = "LVD" & intcounter
Next j
If intcounter 8 Then intcounter = 0
End If
Next cell
End Sub


I'm using two command buttons two start the code. I'd like to be able
to consolidate these actions together. I also have other codes that I
would like the same result for. However I want to hide the counter on
every task other than "Pack"


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