New to vba trying to assign # to strings

  • Thread starter Thread starter hotherps
  • Start date Start date
H

hotherps

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
 
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)
 
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?
 
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)
 
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?
 
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 said:
*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)

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?


Click to expand...
Click to expand...
 
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 said:
*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 > said:
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?



Attachment filename: numberpack.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=41113
Click to expand...
Click to expand...
 
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
Click to expand...
Increment the
number

Your revision just skips cell F5.

--
Regards,
Tom Ogilvy


Bob Phillips said:
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)
Click to expand...
 
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"
 
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)
 
Back
Top