Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range variable not being reset
At the bottom of the outer loop I try to reset the range object rng1, but it
is not happening. I do not understand why and I need to figure out a way to fix this. Can somebody tell me what I am doing wrong? Sub ProcessColumns() Dim rng1 As Range Dim r As Long Dim elements As Integer Dim accounts As String Set ws1 = Worksheets("Control Matrix Fix") ' input data Set ws2 = Worksheets("Import Sheet") ' output data Set rng1 = ws2.Range("A3") ' ws2.Activate With ws1 For r = 3 To 67 elements = listLength(.Cells(r, 4), "|") For I = 0 To elements - 1 rng1.Offset(I, 0) = .Cells(r, 1) ' rng1.Offset(I, 1) = .Cells(r, 2) ' rng1.Offset(I, 2) = .Cells(r, 3) ' accounts = .Cells(r, 4).Value ' rng1.Offset(I, 3).Value = listGetAt(accounts, I + 1, "|") rng1.Offset(I, 4) = .Cells(r, 5) ' rng1.Offset(I, 5) = .Cells(r, 6) ' rng1.Offset(I, 6) = .Cells(r, 7) ' rng1.Offset(I, 7) = .Cells(r, 8) ' rng1.Offset(I, 8) = .Cells(r, 9) ' rng1.Offset(I, 9) = .Cells(r, 10) ' rng1.Offset(I, 10) = .Cells(r, 11) ' rng1.Offset(I, 11) = .Cells(r, 12) ' rng1.Offset(I, 12) = .Cells(r, 13) ' rng1.Offset(I, 13) = .Cells(r, 14) ' rng1.Offset(I, 14) = .Cells(r, 15) ' rng1.Offset(I, 15) = .Cells(r, 16) ' rng1.Offset(I, 16) = .Cells(r, 17) ' rng1.Offset(I, 17) = .Cells(r, 18) ' rng1.Offset(I, 18) = .Cells(r, 19) ' rng1.Offset(I, 19) = .Cells(r, 20) rng1.Offset(I, 20) = .Cells(r, 21) ' rng1.Offset(I, 21) = .Cells(r, 22) ' rng1.Offset(I, 22) = .Cells(r, 23) ' rng1.Offset(I, 23) = .Cells(r, 24) ' rng1.Offset(I, 24) = .Cells(r, 25) ' rng1.Offset(I, 25) = .Cells(r, 26) ' rng1.Offset(I, 26) = .Cells(r, 27) ' rng1.Offset(I, 27) = .Cells(r, 28) ' rng1.Offset(I, 28) = .Cells(r, 29) ' rng1.Offset(I, 29) = .Cells(r, 30) ' rng1.Offset(I, 30) = .Cells(r, 31) ' rng1.Offset(I, 31) = .Cells(r, 32) ' rng1.Offset(I, 32) = .Cells(r, 33) ' rng1.Offset(I, 33) = .Cells(r, 34) ' Next rng1 = rng1.Offset(r + I, 0) ' reset range location THIS IS NOT RESETING THE VALUE OF RNG1 rng1.Activate Next End With End Sub I tried to use rng1.Address = rng1.Offset(r+i,0).address but this appears to be illegal I get an error message on compile that talks about constants. -- Henry Stock, Network Administrator onProject.com 3 Wing Drive Cedar Knolls, NJ 07927-1006 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range variable not being reset
set rng1 = rng1.Offset(r + I, 0)
-- Regards, Tom Ogilvy "Henry Stock" wrote in message ... At the bottom of the outer loop I try to reset the range object rng1, but it is not happening. I do not understand why and I need to figure out a way to fix this. Can somebody tell me what I am doing wrong? Sub ProcessColumns() Dim rng1 As Range Dim r As Long Dim elements As Integer Dim accounts As String Set ws1 = Worksheets("Control Matrix Fix") ' input data Set ws2 = Worksheets("Import Sheet") ' output data Set rng1 = ws2.Range("A3") ' ws2.Activate With ws1 For r = 3 To 67 elements = listLength(.Cells(r, 4), "|") For I = 0 To elements - 1 rng1.Offset(I, 0) = .Cells(r, 1) ' rng1.Offset(I, 1) = .Cells(r, 2) ' rng1.Offset(I, 2) = .Cells(r, 3) ' accounts = .Cells(r, 4).Value ' rng1.Offset(I, 3).Value = listGetAt(accounts, I + 1, "|") rng1.Offset(I, 4) = .Cells(r, 5) ' rng1.Offset(I, 5) = .Cells(r, 6) ' rng1.Offset(I, 6) = .Cells(r, 7) ' rng1.Offset(I, 7) = .Cells(r, 8) ' rng1.Offset(I, 8) = .Cells(r, 9) ' rng1.Offset(I, 9) = .Cells(r, 10) ' rng1.Offset(I, 10) = .Cells(r, 11) ' rng1.Offset(I, 11) = .Cells(r, 12) ' rng1.Offset(I, 12) = .Cells(r, 13) ' rng1.Offset(I, 13) = .Cells(r, 14) ' rng1.Offset(I, 14) = .Cells(r, 15) ' rng1.Offset(I, 15) = .Cells(r, 16) ' rng1.Offset(I, 16) = .Cells(r, 17) ' rng1.Offset(I, 17) = .Cells(r, 18) ' rng1.Offset(I, 18) = .Cells(r, 19) ' rng1.Offset(I, 19) = .Cells(r, 20) rng1.Offset(I, 20) = .Cells(r, 21) ' rng1.Offset(I, 21) = .Cells(r, 22) ' rng1.Offset(I, 22) = .Cells(r, 23) ' rng1.Offset(I, 23) = .Cells(r, 24) ' rng1.Offset(I, 24) = .Cells(r, 25) ' rng1.Offset(I, 25) = .Cells(r, 26) ' rng1.Offset(I, 26) = .Cells(r, 27) ' rng1.Offset(I, 27) = .Cells(r, 28) ' rng1.Offset(I, 28) = .Cells(r, 29) ' rng1.Offset(I, 29) = .Cells(r, 30) ' rng1.Offset(I, 30) = .Cells(r, 31) ' rng1.Offset(I, 31) = .Cells(r, 32) ' rng1.Offset(I, 32) = .Cells(r, 33) ' rng1.Offset(I, 33) = .Cells(r, 34) ' Next rng1 = rng1.Offset(r + I, 0) ' reset range location THIS IS NOT RESETING THE VALUE OF RNG1 rng1.Activate Next End With End Sub I tried to use rng1.Address = rng1.Offset(r+i,0).address but this appears to be illegal I get an error message on compile that talks about constants. -- Henry Stock, Network Administrator onProject.com 3 Wing Drive Cedar Knolls, NJ 07927-1006 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reset a range of cells to zero | Excel Discussion (Misc queries) | |||
reset the range of FOR loop | Excel Discussion (Misc queries) | |||
x y scatter chart series ranges reset to x(range) = y(range) | Charts and Charting in Excel | |||
Public variable is reset after addin macro completes - thread/focus pblm? | Excel Programming | |||
variable that keeps its value until reset | Excel Programming |