vba - VBA在列中匹配相反的符号值

我想知道是否有可能在找到相反符号值的列中创建匹配的宏。这是我堆积的地方：

Sub matching()

Dim i As Integer
Dim myVar, myFunc As Variant
Dim myList As Range

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row Step 1

myVar = Cells(i, "J")
myList = Range(Cells(2, "J"), Cells(Cells(Rows.Count, 1).End(xlUp).Row, "J"))
myFunc = Application.WorksheetFunction.VLookup(myVar, myList, 1, False)

If myVar - myFunc = 0 Then
Cells(i, "G") = "Match"
Else
Cells(i, "G") = "No Match"

End If
Next

End Sub

最佳答案

除了一些语法错误之外，我认为您的代码没有按照您的意愿进行操作。

myVar将指向myList范围的某个单元格。这将使“ VLookup”始终“捕获” myVar单元格和myFunc返回myVar值，从而导致If myVar - myFunc = 0检查以始终返回True

如果您更好地设定目标，则可以根据需要修改以下代码

Sub matching2()

Dim myList As Range
Dim mySht As Worksheet

Set mySht = ThisWorkbook.Worksheets("SheetTest") '<== change "SheetTest" to actual name of your sheet

With mySht
    Set myList = .Range("J2:J" & .Cells(.Rows.Count, 1).End(xlUp).row)
    With .Cells(2, "G")
        .Formula = "=if( vlookup($J2," & "J2:J$" & myList.Rows.Count + 1 & ",1,0) - $J2 = 0,""match"",""no match"") "
        .Copy .Resize(myList.Rows.Count)
    End With
End With


End Sub

没有循环

关于vba - VBA在列中匹配相反的符号值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35625104/