How to skip numbers in a for next loop

[hotherps]

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

 

[BrianB]

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

 

[Serkan]

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

 

[Frank Kabel]

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

 

[hotherps]

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

Thanks