0

In my workbook I have a table with 648K records, I made an iteration to fill an array that I'll use to fill the first column of the table, when I print the values in the range that I want to modify the result only brings the first record of the array:

Private Sub addSuffix()

Dim pasteRange As Range
Dim r, totalRecords As Double
Dim assy, currentRange, array_suffix() As String

totalRecords = WorksheetFunction.CountA(Worksheets("Where Used Top Parent (Table)").Range("A:A")) - 2
    
    ReDim array_suffix(totalRecords)
    
    r = 2
    assy = Worksheets("Where Used Top Parent (Table)").Cells(r, 3).Value
    
    Do While assy <> ""
        
        array_suffix(r - 2) = getSuffix(assy)
        
    r = r + 1
    assy = Worksheets("Where Used Top Parent (Table)").Cells(r, 3).Value
    Loop
    
'This for was a test only the be secure that I was returning the values correctly
    For i = 0 To 10
    Debug.Print (array_suffix(i))
    Next
'**********************************************************************************
    
    currentRange = "A2:A" & CStr(totalRecords + 2)
    Worksheets("Where Used Top Parent (Table)").Range(currentRange).Value = array_suffix()

End Sub

enter image description here enter image description here

What is the correct way to publish the values of my array in the table??

I reed other similar questions but I can't reach the root cause of my error.

3
  • You're working with a 1D array, which is row major (horizontal). The normal approach is to Transpose the array... however, Transpose is limited to 65536 (2^16) elements. Use a 2D array. Commented Dec 15, 2023 at 15:37
  • currentRange = "A2:A" & CStr(totalRecords + 2) ... can be simplified to currentRange = "A2:A" & totalRecords + 2 btw. Commented Dec 15, 2023 at 15:39
  • You are complicating things. Try something like this. Commented Dec 15, 2023 at 16:42

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.