How to skip numbers in a for next loop
 

I have the folowing code that adds a string to the end of each item i
creates which works well. Notice my array value range is from 100 t
144. I now have a need to skip multiple numbers that are included i
that range. i.e. 119-135-137-139

In each case I would just like the code to increment to the nex
permissable value following the logic of the original. (The code look
to see if it is distinct before assigning a cell)


Can't figure it out


Dim arr(100 To 144) As String
Dim arr1, num
Dim rng As Range, cell As Range


For i = 1 To 377
For Each cell In Rows(i).Columns("K:AA")
Set rng = Nothing
If cell.Value = "PACK" Then
Set rng = cell
Exit For
End If
Next
If Not rng Is Nothing Then
For j = 100 To 144 Step 1
num = ""
If Len(Trim(arr(j))) = 0 Then
num = j
arr(num) = "PK"
Exit For
End If
Next
If num = "" Then num = "PPI"
For Each cell In Range(rng, Cells(rng.Row, "AA"))
If cell.Value = "PACK" Then
cell.Value = "PK" & num
End If
Next
End If
Nex

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

How to skip numbers in a for next loop
 

'---------------------------------------------------------------------
For n = 100 To 144
If n < 119 And n < 135 And n < 137 And n < 139 Then
'---------------------------------------------------------------------

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

How to skip numbers in a for next loop
 

Here's a simple revision to your code:

....
For j = 100 To 144 Step 1
If Instr("119-135-137-139",Trim(Str(j))) then Goto skip
num = ""
If Len(Trim(arr(j))) = 0 Then
num = j
arr(num) = "PK"
Exit For
End If
skip: Next
....

Regards


-----Original Message-----
I have the folowing code that adds a string to the end of
each item it
creates which works well. Notice my array value range is
from 100 to
144. I now have a need to skip multiple numbers that are
included in
that range. i.e. 119-135-137-139

In each case I would just like the code to increment to
the next
permissable value following the logic of the original.
(The code looks
to see if it is distinct before assigning a cell)


Can't figure it out


Dim arr(100 To 144) As String
Dim arr1, num
Dim rng As Range, cell As Range


For i = 1 To 377
For Each cell In Rows(i).Columns("K:AA")
Set rng = Nothing
If cell.Value = "PACK" Then
Set rng = cell
Exit For
End If
Next
If Not rng Is Nothing Then
For j = 100 To 144 Step 1
num = ""
If Len(Trim(arr(j))) = 0 Then
num = j
arr(num) = "PK"
Exit For
End If
Next
If num = "" Then num = "PPI"
For Each cell In Range(rng, Cells(rng.Row, "AA"))
If cell.Value = "PACK" Then
cell.Value = "PK" & num
End If
Next
End If
Next


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

.

How to skip numbers in a for next loop
 

Hi
try the following

Dim arr(100 To 144) As String
Dim arr1, num
Dim rng As Range, cell As Range

For i = 1 To 377
For Each cell In Rows(i).Columns("K:AA")
Set rng = Nothing
If cell.Value = "PACK" Then
Set rng = cell
Exit For
End If
Next

If Not rng Is Nothing Then
For j = 100 To 144 Step 1
Case select j
Case 119,135,137,139
'do nothing
Case else
num = ""
If Len(Trim(arr(j))) = 0 Then
num = j
arr(num) = "PK"
Exit For
End If
end Select
Next


If num = "" Then num = "PPI"
For Each cell In Range(rng, Cells(rng.Row, "AA"))
If cell.Value = "PACK" Then
cell.Value = "PK" & num
End If
Next
End If
Next



--
Regards
Frank Kabel
Frankfurt, Germany

I have the folowing code that adds a string to the end of each item
it
creates which works well. Notice my array value range is from 100 to
144. I now have a need to skip multiple numbers that are included in
that range. i.e. 119-135-137-139

In each case I would just like the code to increment to the next
permissable value following the logic of the original. (The code
looks
to see if it is distinct before assigning a cell)


Can't figure it out


Dim arr(100 To 144) As String
Dim arr1, num
Dim rng As Range, cell As Range


For i = 1 To 377
For Each cell In Rows(i).Columns("K:AA")
Set rng = Nothing
If cell.Value = "PACK" Then
Set rng = cell
Exit For
End If
Next
If Not rng Is Nothing Then
For j = 100 To 144 Step 1
num = ""
If Len(Trim(arr(j))) = 0 Then
num = j
arr(num) = "PK"
Exit For
End If
Next
If num = "" Then num = "PPI"
For Each cell In Range(rng, Cells(rng.Row, "AA"))
If cell.Value = "PACK" Then
cell.Value = "PK" & num
End If
Next
End If
Next


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

How to skip numbers in a for next loop
 

Thanks to everyone that responded I used a combination of your code an
it works great.

Thanks

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