Read from Named Range?

[Steph]

Hello. The code below scans the data in column A, and looks for cells where
the right 4 characters are 4040, 4075, 4045 or 8510. If it does, it changes
the sign of the numbers in that row from colums D thru O.
My question is this: Is there a way to enter 4040, 4075, 4045 and 8510 in
cells within a named range called "switch" as opposed to specifically
indentifying them in the code? Thanks!!

Sub ChangeSign()
Dim i As Long
Dim j As Long

Worksheets("Upload Final").Select
For i = 1 To Range("A65536").End(xlUp).Row 'For each row
If Right(Cells(i, 1), 4) = "4040" Or Right(Cells(i, 1), 4) = "4075"
Or Right(Cells(i, 1), 4) = "4045" Or Right(Cells(i, 1), 4) = "8510" Then
For j = 4 To 15
Cells(i, j) = -Cells(i, j)
Next j
End If
Next i
End Sub

 

[Frederick Chow]

Why not.

For Each Cell in Range("Switch").Cells
For i = 1 to Range("A65536").End(xlUp)
if Range("65536").End(xlUp).Cells(i) = Cell Then
' You main code here
end if
Next
Next

Hope this helps

Frederick Chow
Hong Kong

 

[Peter T]

Is there a way to enter 4040, 4075, 4045 and 8510 in

cells within a named range called "switch" as opposed to specifically
indentifying them in the code?

Unless I'm missing something in your question I don't see any way of doing
that, particularly as you are only looking at the last four characters.

If the aim is to speed up your code bear in mind that reading cells is
relatively slow, albeit not as slow as writing. You are reading the same
cell 4 times in each loop, so first assign the cell value to a variable and
process that, eg

dim vCell as variant

' in the loop
vCell = Cells(i, 1)

if right$(vCell, 4) = "4040" Or etc then

Regards,
Peter T