编码VBA excel函数以搜索范围内的字符串 [英] coding a VBA excel function to search a string in a range

问题描述

我是excel的新秀... 我正在尝试创建一个函数，该函数将文本字符串作为参数，对其进行修剪(即删除结尾和开头的多余空格)，在范围内(在另一个电子表格上)搜索字符串的第一个匹配项，并返回该单元格的实际内容. 我已经在下面编写了代码，但是我对其进行了调整，但它永远不会返回任何内容！ 任何帮助将不胜感激！

I am a rookie on excel... I am trying to create a function that takes a text string as parameter, trims it (ie removes the extra spaces at the end and at the beginning), searches for the first occurrence of the string in a range (on another spreadsheet), and returns the actual content of that cell.. I've written the code below but however I tweak it, it never returns anything!! Any help would be much appreciated !

注意:在网上，我发现了几个类似功能的潜艇"实例，但是当我尝试将它们转换为功能"时，它们永远无法工作...

Note: online I've found several examples of "subs" that do similar things, but when I try to convert them to a "function", they never work...

Public Function Find_First2(FindString As String) As String

    Dim Rng As Range

    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

            If Not Rng Is Nothing Then
                Find_First2 = Rng.Value
            Else
                Find_First2 = ""
            End If
        End With
    End If

End Function

推荐答案

您确认修剪不会清空整个字符串，但您仍可以按原样使用它.我做了几件事，但是我不知道应该怎么做.您搜索一个字符串，如果找到它，您是否返回相同的字符串?无论如何，这是代码.我对其进行了测试，并且可以正常工作.现在它将在表Feuil1的A列中查找.进行修改以满足您的需求.

You verify that trimming won't empty the whole string but you still use it as is. I changed a few things, but I don't get what this is supposed to do. You search for a string and if you find it, you return the same string? In any case, here is the code. I tested it and it works. It will look in column A of sheet Feuil1 right now. Modify to suit your needs.

Sub test()
    MsgBox Find_First2("aa")
End Sub

Public Function Find_First2(FindString As String) As String

    Dim Rng As Range
    Dim TrimString As String

    TrimString = Trim(FindString)

    If TrimString <> "" Then
        With Sheets("Feuil1").Range("A:A") 'This is what you need to modify
            Set Rng = .Find(What:=TrimString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

            If Not Rng Is Nothing Then
                Find_First2 = Rng.Value
                MsgBox ("Found at: " & Rng.Address)
            Else
                Find_First2 = ""
            End If
        End With
    End If

End Function

这篇关于编码VBA excel函数以搜索范围内的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！