0 
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

I expected this code to be like this enter image description here

But the result is like this enter image description here

When E = 9, the code runs exactly the same as I thought, but when E is over 10, it doesn't. when E = 9, the V array's values and orders are like this [grape, apple], and that's what I wanted. However, when E = 10, I want the V array's value to be like this [orange] but the result says it is [ , , orange] Could someone tell me what's wrong with my code?

Improve this question
2
  • 1
    Unrelated to your question but I see there are multiple instances of calculation when you are referring to a Cell. In particular, 14 * (E - 9) seems to appear very consistently. You should consider to make a variable and assign the calculation at the start of each loop and just refer to the variable later on. This makes it easier to maintain and also lessen the amount of calculation needed in each loop. Commented Aug 1, 2021 at 14:09
  • It didn't occur to me to make it as a variable! It is a very good idea!! Thank you:) Commented Aug 2, 2021 at 4:23

1 Answer 1

Reset to default
2

For me, the two (different) pictures look like 'Chinese'... So, I cannot refer to them.

  1. No need to use Index, Index2 and Index3. Use only Index and increment it at the second loop end;
'replace this line
Index_3 = Index_3 + 1
'with
Index = Index + 1
' and comment all the previous index incrementations
  1. You must reinitialize the used arrays content and used Index at the first loop end:
'your code...
   If Index = 0 Then Exit Sub 'after this existing code line
   Erase V: Erase P: Erase N: Index = 0
Next
'Your code

Now, using Redim Preserve to often is bad from memory handling point of view. Please, try firstly ReDim to a value to exceed the necessary number of necessary elements (ReDim V(cCount)) and use only of the end: Redim Preserve V(Index -1). Do the same for the other two used arrays...

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

3 Comments

I got stuck on this problem all day long, but finally solved it thanks to you!! Thank you soooo much!!! :)
@김다영 Glad I could help! But I would like to remember you that we here, when somebody answer our question, vote the answer up and more important tick the code left side check box, in order to make it accepted answer. In this way, somebody else searching for a similar issue will know that the offered solution works...
It's been only 3 or 4 days since I started stackoverflow, so I haven't been used to this site's rules. Thank you for letting me know about that! I will do what you told me to :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.