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

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