為什么我的陣列回傳空？以及如何確保它將資料復制到我的第三個選擇中

經過無數次努力將陣列“newvarray”保持在范圍內，我現在遇到了來自 278 行列的空陣列的結果。我相信這也是我的殘局函式未執行的根本原因（將不匹配的值粘貼到卷表中）？

澄清：實際的空單元格將本地人報告為“空”，帶有字串的列報告為“”“”

Dim oldsht As Worksheet
Dim newsht As Worksheet
Dim rollsht As Worksheet

Dim a As Integer
Dim b As Integer
Dim c As Integer

Set oldsht = ThisWorkbook.Sheets("Insert Yesterday's Report Here")
Set newsht = ThisWorkbook.Sheets("Insert Today's Report Here")
Set rollsht = ThisWorkbook.Sheets("Rolls")

Dim OldVArray(), NewVArray(), RollArray() As String

ReDim Preserve OldVArray(1 To oldsht.Range("a" & Rows.Count).End(xlUp).Row - 1, 5 To 5)
ReDim Preserve NewVArray(2 To newsht.Range("a" & Rows.Count).End(xlUp).Row, 5 To 5)
ReDim Preserve RollArray(1 To rollsht.Range("a" & Rows.Count).End(xlUp).Row - 1, 3 To 3)

For a = 2 To oldsht.Range("E" & Rows.Count).End(xlUp).Row
    OldVArray(a, 5) = oldsht.Cells(a, 5)
Next a

For b = 2 To newsht.Range("E" & Rows.Count).End(xlUp).Row
    NewVArray(b, 5) = newsht.Cells(b, 5)
Next b

For c = 2 To rollsht.Range("C" & Rows.Count).End(xlUp).Row
    RollArray(c, 3) = rollsht.Cells(c, 3)
Next c

Dim Voyage As String
For a = 2 To UBound(OldVArray)
    Voyage = OldVArray(a, 5)
    
    For b = 2 To UBound(NewVArray)
    voyage2 = NewVArray(b, 5)
        If voyage2 <> Voyage Then
            If voyage2 <> "" Then
                For Each cell In NewVArray
                voyage2 = rollsheet.Range("C:C")               
                Next
            End If
        End If
    Next
Next

以下是示例創意的片段，突出顯示的是需要找到的行，更改的航程為橙色。Rolls 上的第三個將是宏的輸出。

舊表：

新表：

勞斯萊斯：

uj5u.com熱心網友回復：

未經測驗，但這就是我的做法。只是從你的截圖。如果您的實際資料看起來不同，那么您將需要進行一些調整。

Sub test()
    Dim wb As Workbook, oldsht As Worksheet, newsht As Worksheet, rollsht As Worksheet
    Dim c As Range, id, col, cDest As Range, copied As Boolean, m
    
    Set wb = ThisWorkbook
    Set oldsht = wb.Sheets("Insert Yesterday's Report Here")
    Set newsht = wb.Sheets("Insert Today's Report Here")
    Set rollsht = wb.Sheets("Rolls")
    'next empty row on Rolls sheet
    Set cDest = rollsht.Cells(Rows.Count, "A").End(xlUp).Offset(1)
    'loop colA on new sheet
    For Each c In newsht.Range("A2:A" & newsht.Cells(Rows.Count, "A").End(xlUp).row).Cells
        id = c.Value         'identifier from Col A
        If Len(id) > 0 Then
            m = Application.Match(id, oldsht.Columns("A"), 0) 'check for exact match on old sheet
            If Not IsError(m) Then
                'got a match: check for updates in cols B to C
                copied = False
                For col = 2 To 3
                    If c.EntireRow.Cells(col).Value <> oldsht.Cells(m, col).Value Then
                        If Not copied Then 'already copied this row?
                            cDest.Resize(1, 3).Value = c.Resize(1, 3).Value 'copy changed row
                            Set cDest = cDest.Offset(1) ' next empy row
                            copied = True
                        End If
                        cDest.EntireRow.Cells(col).Interior.Color = vbRed 'flag updated value
                    End If
                Next col
            Else
                cDest.Resize(1, 3).Value = c.Resize(1, 3).Value 'copy new row
                Set cDest = cDest.Offset(1) ' next empy row
            End If
        End If
    Next c
End Sub

標籤：擅长 vba

上一篇：查找并選擇所有不為空的單元格區域

下一篇：WordVBA不會加載MSXML2.DOMDocument物件