Read from Named Range?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steph - 21 Mar 2006 20:19 GMT

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

Reply to this Message

Frederick Chow - 21 Mar 2006 20:32 GMT

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

> Hello. The code below scans the data in column A, and looks for cells
> where
[quoted text clipped - 19 lines]
> Next i
> End Sub

Reply to this Message

Peter T - 21 Mar 2006 20:49 GMT

> 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

> 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
[quoted text clipped - 17 lines]
> Next i
> End Sub

Reply to this Message