DougStroud
Well-known Member
- Joined
- Aug 16, 2005
- Messages
- 2,968
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...
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...
Code:
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