Using values from an array in Offset

[Gara]

I'm trying to use an array to store offset values that refer to cells whose contents I want to change only while my macro is running, and then at the end of the macro I want to revert to the value I had when the macro started.

The cells can only contain "include" or "exclude", which control what series appear in a chart. However for the purposes of the macro I need all of those cells to say "include". To do so I setup this code to change them and populate the array:

k= 0 
Do While k < 6
    'Check row 3 of the column k columns to the left of C
    If Range("C3").Offset(0, k).Value = "Exclude" Then
        'Set the cell back to Include
        Range("C3").Offset(0, k).Value = "Include"
        
        'Resize ResetCells to length l
        ReDim ResetCells(l) As Long
        
        ResetCells(l) = k

        l = l + 1
    End If
    k = k + 1
Loop

This executes without issue (at least to the extent that no errors are thrown by Excel). Later Once I've done what I needed I try to use the array to revert the cells I changed thusly (I never change l's value after the first loop, so that if I, for example, change 3 of the 6 cells I check, l is still 3 when I start this loop):

Do While l - 1 > 0
    'go to the cell indicated by ResetCells(l)
    Range("C3").Offset(0, ResetCells(l)).Value = "Exclude"
    l = l - 1
Loop

When I hit the first line inside the loop (Range("C3"). ...) Excel says the subscript is out of range. I've tried poking around on this an a couple other sites for some guidance, but I'm not seeing anything that seems to help.

Thanks in advance!

 

[Dryver14]

This is not the answer you are looking for but I am just starting out and I would use a work around.

for instance if my table was 30 columns wide i would say

if cells(0,k)=exclude then
cells (0,k)=include
cells(0,100)=Exclude

then to put them back I would look down column 100 for the exclude or number 1 or whatever I put there and use that as a reference to replace cells(0,k)

Long winded I know but simple is as simple does so my mate Forrest says.

 

[Gara]

I had tried something like that, but couldn't get it to work. That was likely because I had to finish by COB yesterday, so I needed some solution and was rushing(ultimately I just didn't reset the cells).

I agree your solution would work just fine in this instance. I'm really mostly just looking to get an understanding of what I did wrong so that, should I need to use an array in the future, I can do so successfuly.

 

[nuked]

Try:

ReDim Preserve ResetCells(l) As Long

 

[nuked]

And ths:

l = l - 1

should probably be l = l +1

Unless you do:

l = ubound(ResetCells)

for i = l to 0 step -1
'do something with ResetCells(i)
next i

 

[Gara]

Thanks nuked!

I needed the Preserve, the other thing I finally noticed is that since I increment l after each new value is added, and never reset it's value to ResetCells' actual length, when I'm pulling the values out I need to be looking at l - 1, not l.

Those two changes made and the code worked perfectly.