Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SKIP PAGE NUMBERS IN EXCEL | Excel Worksheet Functions | |||
my numbers go from 99 and then skip to 1000's? | Excel Discussion (Misc queries) | |||
Sum and Skip Negative Numbers | Excel Worksheet Functions | |||
Average/Min column of numbers, but skip 0's and Null's in the form | Excel Worksheet Functions | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |