Array not loading

[Charles Bushby]

This piece of code is not working properly as the first value loads into the array but no other values load.

I can see that "i" increments to 2 but as soon as it hits the line with "For i = 1 To UBound(aCoupeColAd)" it reverts back to 1 again.

Here is the code if anyone can help work this out for me:

Do
If Not IsEmpty(wShCS.Cells(iRow, iCol + 10)) Then
iCoupeCounter = iCoupeCounter + 1

ReDim Preserve aCoupeColAd(1 To iCoupeCounter)
For i = 1 To UBound(aCoupeColAd) 'FOR SOME REASON ON THE SECOND ITERATION i REVERTS TO 1 AGAIN
aCoupeColAd(i) = iCol + 10
Next i

End If
iCol = iCol + 1
Loop Until iCol = 19

Many thanks in anticipation for any help received.

Charles

 

[RedBeard]

What value does aCoupeColAd hold at that point?

 

[JoeMo]

What is the value of UBound(aCoupeColAd) at that point?

 

[Norie]

Charles

What are you trying to populate the array with?

Perhaps you should be using something other than just i when populating it.

PS Are you sure you need 2 loops?

Do

    If Not IsEmpty(wShCS.Cells(iRow, iCol + 10)) Then
        iCoupeCounter = iCoupeCounter + 1

        ReDim Preserve aCoupeColAd(1 To iCoupeCounter)

        aCoupeColAd(iCoupeCounter) = iCol + 10

    End If

    iCol = iCol + 1
Loop Until iCol = 19

 

[Charles Bushby]

JoeMo, Norrie and Redbeard,

The UBound(aCoupeColAd) holds the first item to be loaded into the array then as soon as i reverts to 1 the array empties itself until it loops round again and the second value the loads into the array in first position. Thus the value fills each time it loops but then empties itself until at the end I am left with the last value only in the array.

The array is loading with whole numbers which are column numbers.

I do need two loops as I am picking up values from a row of cells (which are in 15 contiguous columns) in the spreadsheet of which some are empty. The aim is to enter into the array the column numbers of cells that are not empty.

Charles

 

[Norie]

Charles

What are the 2 loops looping through exactly?

What would the resultant array contain?

Is it the column numbers of the non-empty cells in a specific row?

 

[Charles Bushby]

Norrie,

On reflection maybe I don't need the two loops as I might simply need one loop to go through 15 columns of one row and checks if each cell is empty or not. If not empty then:

1) it adds to the iCoupeCounter in order to increase the array size
2) adds the column number of the filled cell to the array

Not quite sure how to do that!

I will then subsequently need to refer to the iCoupeCounter and the column number in the next part of the code.

Charles

 

[Norie]

Charles

The code I posted kind of does that, I think.

It loops through the row irow and if it finds a cell that's empty redimensions aCoupeColAd and puts icol+10 in the new item of the array.

Mind you, I've got no idea what irow and icol are, or why you are adding 10 to icol.

 

[Charles Bushby]

Norrie,

That works perfectly so thank you very much for your help.

irow and icol are variables to hold row and column numbers

Charles