I have some code that runs down Rows 10-377, and it ranges in column
from K to R. The rows always stay the same but the columns need t
advance to the right. The way I have it now is repeating all of th
same code for new range.
I already have a for statement looks like this:

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

next sub:
For i = 1 To 377
For Each cell In Rows(i).Columns("AI:AJ")
Set rng = Nothing
If cell.Value = "QAPK" Then
Set rng = cell 'y ??
Exit For

The only difference is the new column names.

How can I tell it to loop through multiple ranges without creating
new sub?

Thank

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

What is your goal here? Could be a simple find "QAPK"

--
Don Guillett
SalesAid Software

"hotherps " wrote in message
...
I have some code that runs down Rows 10-377, and it ranges in columns
from K to R. The rows always stay the same but the columns need to
advance to the right. The way I have it now is repeating all of the
same code for new range.
I already have a for statement looks like this:

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

next sub:
For i = 1 To 377
For Each cell In Rows(i).Columns("AI:AJ")
Set rng = Nothing
If cell.Value = "QAPK" Then
Set rng = cell 'y ??
Exit For

The only difference is the new column names.

How can I tell it to loop through multiple ranges without creating a
new sub?

Thanks


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

Soory about that I only pasted a snippet of the code. Here is th
routine, it actually has multiple goals creating numeric assignment
for strings that match. My problem is on the line:

For Each cell In Rows(i).Columns("K:AA")

I need to be able to have this range change after an area has bee
completed. i.e.

K:AA AB:AS AT:BB etc until it reaches EU

I'm not sure how to expand or Loop the statement?

Thanks


Dim arr(100 To 144) As String
Dim arr1, num
Dim rng As Range, cell As Range
Dim i As Long, j As Long
arr1 = Array(125, 127, 129, 131, 133)

For Each cell In Range("K10:DB377")
'If cell.Value = "PPI" Then cell.Value = "PACK"
Next

Application.ScreenUpdating = False

For i = 1 To 377
For Each cell In Rows(i).Columns("K:AA")
Set rng = Nothing
If cell.Value = "QAPK" Then
Set rng = cell 'y ??
Exit For
End If
Next
If Not rng Is Nothing Then
For j = LBound(arr1) To UBound(arr1)
num = ""
If Len(Trim(arr(arr1(j)))) = 0 Then
num = arr1(j)
arr(num) = "QA"
Exit For
End If
Next
If num = "" Then num = "PK"
For Each cell In Range(rng, Cells(rng.Row, "AA"))
If cell.Value = "QAPK" Then
cell.Value = "QA" & num
End If
Next
End If
Next

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

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
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
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

Very weird code, it don't do anything.

Why not try to record doing a find and replace, for each of your strings to get the code?


Regards Robert


"hotherps " wrote in message ...
I have some code that runs down Rows 10-377, and it ranges in columns
from K to R. The rows always stay the same but the columns need to
advance to the right. The way I have it now is repeating all of the
same code for new range.
I already have a for statement looks like this:

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

next sub:
For i = 1 To 377
For Each cell In Rows(i).Columns("AI:AJ")
Set rng = Nothing
If cell.Value = "QAPK" Then
Set rng = cell 'y ??
Exit For

The only difference is the new column names.

How can I tell it to loop through multiple ranges without creating a
new sub?

Thanks


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



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004