Excel填充数组缺少值的范围 [英] Excel Fill range with missing values of an array

问题描述

所以我正在寻找一个基本上选择一个范围的宏，将单元格中的值与来自数组的值进行比较，并将缺少的值添加到范围的末尾。

解决方案

使用字符串尝试此示例：

为了让您开始，您可以使用单元格，列＃），而不是Range（A1），并执行循环遍历数组并逐行循环，比较每个值。然后将不匹配的值添加到新数组中，稍后您将在另一个循环中将其返回到表中。

假设您的值从Sheets（工作表Sheet1 ），范围（ A1\" ）。以下示例名称：
Sam，
Jim，
Stanly，
Jeff，
Mike，
Jeff，
Toby。 p>

  Dim lastRow As Integer 
 Dim i As Integer 
 Dim r As Integer 
 Dim n As Integer 
 Dim unMatchedArray（）As String 
 
 Private Sub ArrayChecker（）
 
 Dim myArray（7）As String 
 Dim match As Boolean 
 
n = 0 
 
 myArray（0）=Jeff
 myArray（1）=Stanly
 myArray（2）=Mike
 myArray（3）=Sam
 myArray（4）=Toby
 myArray（5）=Reginald
 myArray（6）=Wolfgang
 myArray 7）=手动
 
调用GetLastRow 
 
对于i = 0 To UBound（myArray）
 
r = 1 
 match = False 
对于r = 1 To lastRow 
如果myArray（i）= Sheets（Sheet1）。单元格（r，1）然后
 match = True 
退出
结束如果
下一步r 
 
如果m atch = False然后
 ReDim保留unMatchedArray（n）
 unMatchedArray（n）= myArray（i）
n = n + 1 
结束如果
 
下一步i 
 
n = n  -  1 
 
如果n> 0然后
调用AddToSheet 
 End If 
 
 End Sub 
 
 Private Sub GetLastRow（）
 
' A1：A65536）
 lastRow = Sheets（Sheet1）。单元格（65536，1）.End（xlUp）.Row 
 
 End Sub 
 
私人Sub AddToSheet（）
 
调用GetLastRow 
r = lastRow + 1 
i = 0 
 
对于i = 0到n 
表单（Sheet1 ）.Cells（r，1）= unMatchedArray（i）
r = r + 1 
 Next i 
 
 End Sub 
  / pre> 
So i am looking to write a macro that would basically select a range, compare the values in the cells with values from say an array and add the missing ones to the end of the range.
解决方案
Try this example using strings:

To get you started, you could use Cells(row#, column#), instead of Range("A1")  and perform loops over the array and loop through the cells by row, comparing each value.  Then adding the values that don't match to a new array, that you will later pass back into the sheet in another loop.

Assuming your values start at Sheets("Sheet1").Range("A1"). With the following example names:
Sam,
Jim,
Stanly,
Jeff,
Mike,
Jeff,
Toby.
Dim lastRow As Integer
Dim i As Integer
Dim r As Integer
Dim n As Integer
Dim unMatchedArray() As String

Private Sub ArrayChecker()

    Dim myArray(7) As String
    Dim match As Boolean

    n = 0

    myArray(0) = "Jeff"
    myArray(1) = "Stanly"
    myArray(2) = "Mike"
    myArray(3) = "Sam"
    myArray(4) = "Toby"
    myArray(5) = "Reginald"
    myArray(6) = "Wolfgang"
    myArray(7) = "Manual"

    Call GetLastRow

    For i = 0 To UBound(myArray)

        r = 1
        match = False
        For r = 1 To lastRow
            If myArray(i) = Sheets("Sheet1").Cells(r, 1) Then
                match = True
                Exit For
            End If
        Next r

        If match = False Then
            ReDim Preserve unMatchedArray(n)
            unMatchedArray(n) = myArray(i)
            n = n + 1
        End If

    Next i

    n = n - 1

    If n > 0 Then
        Call AddToSheet
    End If

End Sub

Private Sub GetLastRow()

    ' checking Range("A1:A65536")
    lastRow = Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row

End Sub

Private Sub AddToSheet()

    Call GetLastRow
    r = lastRow + 1
    i = 0

    For i = 0 To n
        Sheets("Sheet1").Cells(r, 1) = unMatchedArray(i)
        r = r + 1
    Next i

End Sub


                        
这篇关于Excel填充数组缺少值的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！