VBA not reading all data in array

Question

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

What does debug show for UBound(data, 1) value? And Rows.Count — dbmitch
– dbmitch, Commented Jun 2, 2022 at 15:40
You have data = ws2.Range("A2:G" & ws1.Cells(Rows.Count, 2).End(xlUp).Row).Value . You probably need ws2.Cells(Rows.Count, 2) instead. — ouroboros1
– ouroboros1, Commented Jun 2, 2022 at 15:52

postmaster 420 · Accepted Answer · 2022-06-03 17:11:23Z

1

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

edited Jun 3, 2022 at 17:11

postmaster 420

1078 bronze badges

answered Jun 2, 2022 at 15:58

PeterT

8,5971 gold badge19 silver badges46 bronze badges

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

5 Comments

postmaster 420 Over a year ago

How to make "Cells(n, 10).Value " containing unique value. Currently i see duplicate value separated by comma.

PeterT Over a year ago

From your own code, you are combining Cells(n, 10) with the value from data(i,2). You haven't clearly specified what you want that may be different from that. If it's a different column, then just change the row/column values.

postmaster 420 Over a year ago

Initially that column Cells(n, 10) is empty. I am grabbing value from data sheet then updating Cells(n, 10). Is there a way put all value in memory then remove duplicates then at end update Cells(n, 10) ?? If there multiple value separate them by comma.

PeterT Over a year ago

Without seeing your data, it very unclear what you're trying to do. If the code itself is incorrect and doesn't match the patterns in the data, no one here will be able to advise you on how to correct your code. You can always pull the main sheet data into an array also and make your comparisons there (with a large set of data, this will be faster anyway). Again, you have to adjust the code example to your data.

postmaster 420 Over a year ago

if u see column J in attached image. How "windows file system" and "windows vss" appearing multiple times in a cell. I want them only once then separated by comma.

Collectives™ on Stack Overflow

VBA not reading all data in array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related