Read from Named Range?

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

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

"Steph" <noreply@nowhere.com> wrote in message
news:O0azixRTGHA.1236@TK2MSFTNGP11.phx.gbl...
> 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
>
>

> 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

"Steph" <noreply@nowhere.com> wrote in message
news:O0azixRTGHA.1236@TK2MSFTNGP11.phx.gbl...
> 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
>
>