Function to find table names

[Psynomi]

Hello,
I am looking for a code to find the name of a table that contains a specific cell value.
Let me explain, I have a table that lists certain cell values. These values also occur in other tables in another worksheet. In my first table I want to add the table names of the tables in the other worksheet that contain these cell values.

So far I've gotten this far:

Function CloneName(FindString As String)
Dim Rng As Range
Dim Clone As String

If Trim(FindString) <> "" Then
With Sheets("Clones")
[FONT=&quot]Set Rng = .Find(FindString)[/FONT]
If Not Rng Is Nothing Then
Clone = Rng.ListObject.Name
Else
Clone = ""
[FONT=&quot]End If[/FONT]
[FONT=&quot]End With[/FONT]
[FONT=&quot]End If[/FONT]

CloneName = Clone

End Function

Unfortunately something in this code doesn't work and I can't figure out what. The .Find part works, because for those values that don't occur in the other worksheet I get a blank as I should. So I guess I'm doing something wrong in the ListObject statement, but however I rephrase I can't get it to work.

Any help would be thoroughly appreciated. I hope I was clear in what I'm looking for.

 

[ChrisM]

That looks fine, except you are not allowing for the case that you search for a text string, and you find it and it's NOT in a valid Table range.

Would be helpful to know what error you are getting!

 

[Psynomi]

Thank you. This morning after a fresh look at it and incorporation of a fail-safe incase the string wasn't in a table I was able to get it working using the following code:

[FONT=&quot]Function CloneName(FindString As String)[/FONT]
[FONT=&quot] Dim Rng As Range[/FONT]
[FONT=&quot] Dim CloneFind As String[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] If Trim(FindString) <> "" Then[/FONT]
[FONT=&quot] With Sheets("Clones").Range("C:C")[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Set Rng = .Find(What:=FindString)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] If Not Rng Is Nothing Then[/FONT]
[FONT=&quot] CellInTable = (Rng.ListObject.Name <> "")[/FONT]
[FONT=&quot] If CellInTable = True Then[/FONT]
[FONT=&quot] CloneFind = Rng.ListObject.Name[/FONT]
[FONT=&quot] Else[/FONT]
[FONT=&quot] CloneFind = ""[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] Else[/FONT]
[FONT=&quot] CloneFind = ""[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]End With[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]End If[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] CloneName = CloneFind[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]End Function[/FONT]

Thanks for your advice ChrisM!