CODEWITHSUNDEEP
arraysexcelvba
J4C3N-14
J4C3N-14

Reputation: 696

Unable to store values in an Array

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.

Upvotes: 1

Views: 38

Answers (1)

Tim Williams
Tim Williams

Reputation: 166221

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

Upvotes: 2

Related Questions