我按第一列比較兩張表，然后從第 10 列獲取值。但它沒有回圈遍歷資料表中的所有值。
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
uj5u.com熱心網友回復：
這可能有助于解決我在您的代碼中看到的幾個問題。
- 在自己的行上宣告每個變數，并盡可能靠近它首次使用的位置
你的線
Dim i, n As Long, ws1 As Worksheet, ws2 As Worksheet, data, client, mainsht
宣告i為 a Variant, nas Long, and data, client, 和mainshtall as Variants. 這可能不是你想要的。
- 始終非常清楚您參考的是哪個作業簿、作業表或范圍
這條線
data = ws2.Range("A2:G" & ws1.Cells(Rows.Count, 2).End(xlUp).Row).Value
使用Rows.Count當前活動作業表中的 。這可能是也可能不是ws2作業表。所以要小心并清楚地鏈接參考。
- 由于您的評論提到了主作業表，因此將其用作變數名。通常，為您的變數使用描述性名稱以使您的代碼更好地“閱讀”（即自我記錄）。
下面的這個例子展示了如何組合這些點。不確定它是否回答了您的問題，但它確實解決了這些潛在問題。
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/485422.html