这个东西,你录个宏不就解决了? Option Explicit Function FindExcelStr(ByVal fStr As String) As String ''演示在A列中查找相应的内容,如果没有找到则添加 Dim Rng As Excel.Range, Rng2 As Excel.Range, k As Long Set Rng = Sheet1.Columns("a:a") Set Rng2 = Rng.Find(fStr) If Rng2 Is Nothing Then ''如果没找到则添加 k = Sheet1.Range("a" & Sheet1.Cells.Rows.Count).End(xlUp).Row ''最后一行数据行 If k = 1 Then If Range("a1") = "" Then k = 0 Sheet1.Cells(k + 1, 1) = fStr FindExcelStr = "new" ''表示新添加的 Else FindExcelStr = Rng2.Address ''返回地址 End If End Function Sub test() Debug.Print FindExcelStr("www") End Sub
如果是单元格匹配:使用 Set Rng2 = Rng.Find(fStr, LookAt:=xlWhole)
Option Explicit
Function FindExcelStr(ByVal fStr As String) As String
''演示在A列中查找相应的内容,如果没有找到则添加
Dim Rng As Excel.Range, Rng2 As Excel.Range, k As Long
Set Rng = Sheet1.Columns("a:a")
Set Rng2 = Rng.Find(fStr)
If Rng2 Is Nothing Then
''如果没找到则添加
k = Sheet1.Range("a" & Sheet1.Cells.Rows.Count).End(xlUp).Row ''最后一行数据行
If k = 1 Then If Range("a1") = "" Then k = 0
Sheet1.Cells(k + 1, 1) = fStr
FindExcelStr = "new" ''表示新添加的
Else
FindExcelStr = Rng2.Address ''返回地址
End If
End Function
Sub test()
Debug.Print FindExcelStr("www")
End Sub