Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read from Named Range?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read from Named Range?
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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
How read a csv file to load named cells | Excel Programming | |||
If any cell in named range = 8 then shade named range | Excel Programming | |||
Modify "pick from list" to read named range | Excel Programming |