在excel vba中重复时如何擦除数组的先前值

2024-05-20 • 问答

Sub WriteToZeroBasedArray()

Dim E As Integer
Dim rCount As Long: rCount = Worksheets("0618").Cells.SpecialCells(xlLastCell).Row
For E = 9 To 11
 
    Dim V() As Variant ' Note the parentheses!
    Dim P() As Variant
    Dim N() As Variant
    Dim Index As Long
    Dim Index_2 As Long
    Dim Index_3 As Long
    Dim cCount As Long: cCount = Worksheets("0618").Cells.SpecialCells(xlLastCell).Column ' e.g.
    
    Dim c As Long '배열에 값 저장하기
    For c = 7 To cCount
        If 0 < Worksheets("0618").Cells(E,c).Value And Worksheets("0618").Cells(E,c).Value < 100 Then
            ReDim Preserve V(Index)
            ' A safer way (Option Base related):
            'ReDim Preserve V(0 To Index)
            V(Index) = Worksheets("0618").Cells(2,c).Value
            Index = Index + 1
            
            ReDim Preserve P(Index_2)
            P(Index_2) = Worksheets("0618").Cells(4,c).Value
            Index_2 = Index_2 + 1
            
            ReDim Preserve N(Index_3)
            N(Index_3) = Worksheets("0618").Cells(E,c).Value
            Index_3 = Index_3 + 1
        End If
    Next c
    
     
    Dim K As Integer
    Dim L As Integer
    
    K = UBound(V)  '배열의 값 차례로 Sheet2에 넣기
    For L = 0 To K
      If L < 5 Then
        Worksheets("sheet2").Cells(L + 6 + 14 * (E - 9),1).Value = V(L)
        Worksheets("sheet2").Cells(L + 6 + 14 * (E - 9),2).Value = P(L)
        Worksheets("sheet2").Cells(L + 6 + 14 * (E - 9),3).Value = N(L)
      Else
        Worksheets("sheet2").Cells(L + 1 + 14 * (E - 9),5).Value = V(L)
        Worksheets("sheet2").Cells(L + 1 + 14 * (E - 9),6).Value = P(L)
        Worksheets("sheet2").Cells(L + 1 + 14 * (E - 9),7).Value = N(L)
      End If
        
    Next
    
    Dim FP As Integer
    Dim Sum As Integer
      Sum = 0
    '수량*단가 구하기
    For FP = (6 + 14 * (E - 9)) To (10 + 14 * (E - 9))
      Worksheets("sheet2").Cells(FP,4).Value = Worksheets("sheet2").Cells(FP,2).Value * Worksheets("sheet2").Cells(FP,3).Value
      Worksheets("sheet2").Cells(FP,8).Value = Worksheets("sheet2").Cells(FP,6).Value * Worksheets("sheet2").Cells(FP,7).Value
      
      '총액 구하기
      Sum = Sum + Worksheets("sheet2").Cells(FP,4).Value + Worksheets("sheet2").Cells(FP,8).Value
      
      '0 지우기
      If Worksheets("sheet2").Cells(FP,4).Value = 0 Then
        Worksheets("sheet2").Cells(FP,4).ClearContents
      End If
      If Worksheets("sheet2").Cells(FP,8).Value = 0 Then
        Worksheets("sheet2").Cells(FP,8).ClearContents
      End If
    Next
    
    '총액 구하기2
    Worksheets("sheet2").Cells(12 + 14 * (E - 9),8).Value = Sum
    
    '받으실 분
    Worksheets("sheet2").Cells(3 + 14 * (E - 9),2).Value = Worksheets("0618").Cells(E,3).Value
    
    '주소
    Worksheets("sheet2").Cells(3 + 14 * (E - 9),5).Value = Worksheets("0618").Cells(E,2).Value
    
    '서식 복사'
    Worksheets("sheet3").Range("A1:H13").Copy
    Worksheets("sheet2").Range("A" & 15 + 14 * (E - 9)).PasteSpecial
    
    
    
    If Index = 0 Then Exit Sub
    
    Next
    
    Debug.Print Join(V,vbLf)
    
    
    
    End Sub

我希望这段代码是这样的

但是结果是这样的

当 E = 9 时，代码运行和我想象的完全一样，但是当 E 超过 10 时，它不会。当 E = 9 时，V 数组的值和顺序是这样的 [grape,apple]，这就是我想要的。但是，当 E = 10 时，我希望 V 数组的值是这样的 [orange] 但结果说它是 [,橙色] 有人能告诉我我的代码有什么问题吗？

对我来说，这两张（不同的）图片看起来像“中国人”......所以，我不能参考它们。

无需使用 Index、Index2 和 Index3。仅使用 Index 并在第二个循环结束时将其递增；

'replace this line
Index_3 = Index_3 + 1
'with
Index = Index + 1
' and comment all the previous index incrementations

您必须重新初始化使用过的数组内容并在第一个循环结束时使用 Index：

'your code...
   If Index = 0 Then Exit Sub 'after this existing code line
   Erase V: Erase P: Erase N: Index = 0
Next
'Your code

现在，从内存处理的角度来看，经常使用 Redim Preserve 是不好的。请首先尝试将 ReDim 设置为超过必要元素数量 (ReDim V(cCount)) 的值，并仅使用结尾：Redim Preserve V(Index -1)。对其他两个使用过的数组做同样的事情...

在excel vba中重复时如何擦除数组的先前值

qq345289212 回答：在excel vba中重复时如何擦除数组的先前值

大家都在问