时间戳

首页 > 解决方案 > 无法将值存储在数组中

arrays - 无法将值存储在数组中

问题描述

有时此代码有效，有时则无效。我希望存储在数组中计数的值，然后存储Sum它们。但是它不起作用。请原谅我的代码。

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

我哪里出错了？谢谢你。

不工作我的意思是：它不会将 PHoursCnt 存储在数组中，数组是空的，但最后一次计数。

标签： arraysexcelvba

解决方案

您正在使用错误的值调整数组的大小：

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

推荐阅读