VBA not reading all data in array-CodePudding

I am compaining two sheet by first column then getting value from 10th column.But its not looping thru all value from data sheet.

Sub Summarize()
    Dim i, n As Long, ws1 As Worksheet, ws2 As Worksheet, data, client, mainsht
        
    Set ws1 = ThisWorkbook.sheets("Sheet1")
    Set ws2 = ThisWorkbook.sheets("Sheet2")
    
    data = ws2.Range("A2:G" & ws1.Cells(Rows.Count, 2).End(xlUp).Row).Value 'data to array
    For n = 2 To ws1.Range("A2").End(xlDown).Row                'loop over rows in the mainsheet (sheet1)
             
             For i = 1 To UBound(data, 1)                       'loop over rows in the array in data (sheet2)
             Debug.Print data(i, 1)
              If ws1.Cells(n, 1) = data(i, 1) Then      'if client name from mainsheet matches to data sheet
                          
                             ws1.Cells(n, 10) = ws1.Cells(n, 10) & " " & data(i, 2) & ","
              End If
           Next
    Next
       
End Sub

CodePudding user response：

This may help with a couple problems I see with your code.

Declare each variable on its own line and as close as possible to where it's first used

Your line

Dim i, n As Long, ws1 As Worksheet, ws2 As Worksheet, data, client, mainsht

declares i as a Variant, n as Long, and data, client, and mainsht all as Variants. That may not exactly be what you want.

Always be very clear about which workbook, worksheet, or range you are referencing

This line

data = ws2.Range("A2:G" & ws1.Cells(Rows.Count, 2).End(xlUp).Row).Value

uses the Rows.Count from whatever the currently active worksheet. This may or may not be the ws2 worksheet. So be careful and clearly link the references.

Since your comments mention the main sheet, use that as a variable name. Generally, use descriptive names for your variables to make your code "read" better (i.e. self-documenting).

This example below shows how to combine these points. Not sure if it answers your question, but it does clear up those potential problems.

Option Explicit

Sub Summarize()
    Dim mainWS As Worksheet
    Dim dataWS As Worksheet
    Set mainWS = ThisWorkbook.Sheets("Sheet1")
    Set dataWS = ThisWorkbook.Sheets("Sheet2")
    
    '--- copies the data into a memory-based array
    Dim dataLastRow As Long
    Dim data As Variant
    With dataWS
        dataLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        data = dataWS.Range(.Cells(2, 1), .Cells(dataLastRow, "G")).Value
    End With
    
    Dim mainLastRow As Long
    With mainWS
        mainLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    
        Dim n As Long
        Dim i As Long
        For n = 2 To mainLastRow
            For i = 1 To UBound(data, 1)
                Debug.Print data(i, 1)
                If .Cells(n, 1).Value = data(i, 1) Then
                    .Cells(n, 10).Value = .Cells(n, 10).Value & " " & data(i, 2) & ","
                End If
            Next i
        Next n
    End With
End Sub