将用户定义的范围作为输入来运行

I am relatively new to VBA in excel. I would like to write a custom function that takes a range defined by the user as input and then searches for a particular word within that range. So far, the following code works, but it only searches in the predefined range "a2:g2", but I would want something that would change to "a3:g3", etc as the user fills down:

我在excel中对VBA比较新。我想编写一个自定义函数，它将用户定义的范围作为输入，然后搜索该范围内的特定单词。到目前为止，以下代码有效，但它只搜索预定义的范围“a2：g2”，但我希望在用户填写时将某些内容更改为“a3：g3”等：

Function findme()
    With Worksheets("Filtered").Range("a2:g2")
    Set c = .Find("XXXX", LookIn:=xlValues)
    If Not c Is Nothing Then
      findme = "Match Found"
    Else: findme = "No match"
    End If
End With

I thought this should work, but it does not. It returns "VALUE"

我认为这应该有效，但事实并非如此。它返回“VALUE”

Function findme(myrange as range)
    With Worksheets("Filtered").Range(myrange)
    Set c = .Find("XXXX", LookIn:=xlValues)
    If Not c Is Nothing Then
      findme = "Match Found"
    Else: findme = "No match"
    End If
End With

1 个解决方案

#1

myrange doesn't get passed to the function as an address / string, it's an actual range. The way you use it in your code (Worksheets("Filtered").Range(myrange)) requires an address. You could keep your code and use myrange.address instead of just myrange, or you can use the fact that your variable is all you need to continue.

myrange不会作为地址/字符串传递给函数，它是一个实际范围。在代码中使用它的方式（Worksheets（“Filtered”）。Range（myrange））需要一个地址。你可以保留你的代码并使用myrange.address而不仅仅是myrange，或者你可以使用你的变量就是你需要继续的事实。

Function findme(myrange as range) as string
    With myrange
    Set c = .Find("XXXX", LookIn:=xlValues)
    If Not c Is Nothing Then
      findme = "Match Found"
    Else: findme = "No match"
    End If
End With