arrays - 定义数组搜索的术语

Question

我一直在寻找这个问题的答案，但我找不到任何足够具体的内容来填补我的 VBA 知识空白。

我将两个数据列表放入数组中，以便使用代码的修改版本进行比较 here(我将在下面发布)。

但是，我不想将整个单元格输入到数组中以与第二个数组进行比较。例如，如果第一个工作表中的单元格显示“Company, LLC”，我只想搜索“Company”。我有一些代码可以执行此操作:

 s = rCell.Value
    indexofthey = InStr(1, s, ",")
    aftercomma = Right(s, Len(s) - indexofthey + 1)
    celld = Left(s, Len(s) - Len(aftercomma))

我需要以某种方式处理这个问题的代码(从我上面链接的问题的答案中复制)是这样的:

    Option Explicit

Private Sub cmdCompare2to1_Click()

Dim sheet1 As Worksheet, sheet2 As Worksheet, sheet3 As Worksheet
Dim lngLastR As Long, lngCnt As Long
Dim var1 As Variant, var2 As Variant, x
Dim rng1 As Range, rng2 As Range


Set sheet1 = Worksheets(1)
Set sheet2 = Worksheets(2)
Set sheet3 = Worksheets(3) ' assumes sheet3 is a blank sheet in your workbook

Application.ScreenUpdating = False

'let's get everything all set up
'sheet3 column headers
sheet3.Range("A1:B1").Value = Array("in1Not2", "in2Not1")

'sheet1 range and fill array
With sheet1

    lngLastR = .Range("A" & .Rows.Count).End(xlUp).Row

    Set rng1 = .Range("A1:A" & lngLastR)
    var1 = rng1

End With

'sheet2 range and fill array
With sheet2

    lngLastR = .Range("A" & .Rows.Count).End(xlUp).Row

    Set rng2 = .Range("A1:A" & lngLastR)
    var2 = rng2

End With

'first check sheet1 against sheet2
On Error GoTo NoMatch1
For lngCnt = 1 To UBound(var1)

    x = Application.WorksheetFunction.Match(var1(lngCnt, 1), rng2, False)

Next


'now check sheet2 against sheet1
On Error GoTo NoMatch2
For lngCnt = 1 To UBound(var2)

    x = Application.WorksheetFunction.Match(var2(lngCnt, 1), rng1, False)

Next

On Error GoTo 0
Application.ScreenUpdating = True
Exit Sub

NoMatch1:
    sheet3.Range("A" & sheet3.Rows.Count).End(xlUp).Offset(1) = var1(lngCnt, 1)
    Resume Next


NoMatch2:
    sheet3.Range("B" & sheet3.Rows.Count).End(xlUp).Offset(1) = var2(lngCnt, 1)
    Resume Next


End Sub

Answer 1

假设您不想更改单元格中的值，则需要循环遍历数组。您可以使用这样的过程:

Sub RemoveUnwantedText(ByRef theArray As Variant)

Dim theValue As String
Dim i As Long
Dim indexOfComma As Integer
    ' array is created from single-column range of cells
    ' and so has 2 dimensions
    For i = LBound(theArray, 1) To UBound(theArray, 1)
        theValue = CStr(theArray(i, 1))
        indexOfComma = InStr(1, theValue, ",")
        If indexOfComma > 0 Then
            theValue = Trim(Left(theValue, indexOfComma - 1))
        End If
        theArray(i, 1) = theValue
    Next i

End Sub

将其粘贴到与您的代码相同的模块中。在您的代码中，在进行任何比较之前，添加以下调用:

RemoveUnwantedText var1
RemoveUnwantedText var2