Looping through Block of Code

[DougStroud]

I have a large number of lines of code that I need to repeat on another sheet. The criteria for the code is the same, but the parameters are different, i.e. the last row, the name of the sheet, etc...
Rather than creating another procedure I was curious if I can loop through the following code and created new variable, sheet objects to carry out the instructions on the other sheet?
The following code is significantly reduced- I have about 500 total lines of replace, so don't gauge it soley by what you see here-- I did this to save everyone the pain...

Sub ColorsColorFF()
     Dim Ws As Worksheet, c As Range, rng As Range
     Dim LRow As Long, LRow1 As Long, LRow2 As Long
     Dim i As Long

     Set Ws = ActiveSheet
     LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

     Application.ScreenUpdating = False

For Each c In Range("N4:N" & LRow)
        c.Formula = " " & c & " "
    Next c

    With Ws
          For Each c In Intersect(Ws.Columns(14), Ws.UsedRange)
'Workbook("Color Subs.xls").Worksheets(
'Single Color Term Swap- #3
If InStr(c.Value, " 60S ") > 0 Then c.Formula = Replace(c.Value, " 60S ", "Denim-Washes")
If InStr(c.Value, " Amber ") > 0 Then c.Formula = Replace(c.Value, " Amber ", "Yellow")
If InStr(c.Value, " Amethyst ") > 0 Then c.Formula = Replace(c.Value, " Amethyst ", "Purple")
If InStr(c.Value, " Aqua ") > 0 Then c.Formula = Replace(c.Value, " Aqua ", "Turquoise")
If InStr(c.Value, " Argyle ") > 0 Then c.Formula = Replace(c.Value, " Argyle ", "Print")
     End With

For Each c In Range("N4:N" & LRow)
  c.Value = Application.WorksheetFunction.Trim(c.Value)
Next c


'     For Each c In rng
'            c.Value = Application.WorksheetFunction.Trim(c.Value)
'            Next c

Application.ActiveSheet.Calculate
Application.ScreenUpdating = True
                  
End Sub

 

[jindon]

OK

Look at the Local Window and find out "myList" and click on "+" sing, so that you can see inside that variable.

myList is an array generated from the range of your "List".

LBound function return Lower bound of an array and UBound returns Upper bound of an array.
So it will loop through the array from the Lowest index, 1 in this case, to the greatest index in an array.
LBound to UBound means all the through in an array.
Because myList has two columns, myList(index,1) = what you have in 1 st column, myList(index,2) = what you have in 2nd column.

You can test it for yourself, something like

For i = 3 To 5, it only loops from 3rd to 5th row in an array.

Does this explain to you?

 

[DougStroud]

Thanks J-
I will have a go. I am finishing up some details from last night. I will most likely work on this tomorrow and post back. I have to tidy up all this code. This project for me is finally almost ready to publish.... been a long time- about a year now. A lot of my code is directly from you, I denote who gives me what, and your name is on a lot of pieces and a lot of big ones. I can say honestly that I did not understand anything you were doing for me initially, but now I have a little bit of understanding. Thanks for all the assistance over the past year.