Unable to store values in an Array

Question

Sometime this code works and sometimes it does not. I wish to store the values counted in the array and then Sum them. However it is not working. Please excuse my code.

Dim PaintWWArray() As Variant
Dim PHoursCnt As Long
Dim PaintWWCnt As Long

Set srchRng = ActiveSheet.Range(rangeString)
Set rngfindValue = srchRng.Find(what:="AD PAINTING W/W", Lookat:=xlPart)

'Find all the Tasks and Hours
If Not rngfindValue Is Nothing Then
   rngFirstAddress = rngfindValue.Address
    Do
        PaintWWCnt = PaintWWCnt + 1
        PHoursCnt = rngfindValue.Offset(0, 4).Value

         ReDim Preserve PaintWWArray(PHoursCnt)
         PaintWWArray(PHoursCnt) = PHoursCnt

         Set rngfindValue = srchRng.FindNext(rngfindValue)

    Loop Until rngfindValue Is Nothing Or rngfindValue.Address = rngFirstAddress

     PHoursCnt = Application.WorksheetFunction.Sum(PaintWWArray)

      Worksheets("Weekly Report Data").Range("C6").Value = PaintWWCnt
      Worksheets("Weekly Report Data").Range("D6").Value = PHoursCnt

     Debug.Print PHoursCnt
End If

Where have I gone wrong? Thank you.

By Not working I mean: It does not store the PHoursCnt in the array, the array is empty but for the last counted.

Sure, basically the array is 'empty' apart from the 'last' added. — J4C3N-14
– J4C3N-14, Commented May 10, 2020 at 16:57
ReDim Preserve PaintWWArray(PHoursCnt) is PHoursCnt correct here? Seems like you might have intended to use PaintWWCnt — Tim Williams
– Tim Williams, Commented May 10, 2020 at 17:02
The PaintWWCnt is 'adding' on each pass to get the number of how many times it appears, the PHoursCnt is 'meant' to be adding a number from .Offset(0, 4) on each pass into the array. — J4C3N-14
– J4C3N-14, Commented May 10, 2020 at 17:08
@TimWilliams I think you nailed it, but then it seems odd that it would be described as an intermittent error. — John Coleman
– John Coleman, Commented May 10, 2020 at 17:09

Tim Williams · Accepted Answer · 2020-05-10 17:14:16Z

2

You're resizing the array using the wrong value:

If Not rngfindValue Is Nothing Then
   rngFirstAddress = rngfindValue.Address
    Do
        PaintWWCnt = PaintWWCnt + 1
        ReDim Preserve PaintWWArray(PaintWWCnt) '<<<<<< not PHoursCnt

        PHoursCnt = rngfindValue.Offset(0, 4).Value
        PaintWWArray(PHoursCnt) = PHoursCnt

        Set rngfindValue = srchRng.FindNext(rngfindValue)

    Loop Until rngfindValue Is Nothing Or rngfindValue.Address = rngFirstAddress

answered May 10, 2020 at 17:14

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

J4C3N-14 Over a year ago

OH! yes I was sizing it according to the value and not how many iterations there were...is my understanding correct?

Tim Williams Over a year ago

Yes that's the problem

J4C3N-14 Over a year ago

Thank you! Accepted!

Collectives™ on Stack Overflow

Unable to store values in an Array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related