Error with Find (438)

[Newton/sqm]

I have been working on the same roblem for days now and no one could help me so far. I have a userform and the user inserts a certain identification number and some data associated with it. The code should find the ID in a table called "Tests_results", which is in the "Tests Results" Worksheet. As soon as the ID is found, the row is updated with the data metioned above. My code looks like:



Option ExplicitPrivate Sub CommandButton4_Click()
Dim Obj As Range


Set Obj = Sheets("Tests Results").ListObjects("Test_results").Find(SampleID.Value) 'error 438 in this line


With Obj

.ListColumns("Flammability Type ") = Me.ComboBoxFlamm.Value ' this is the data to be updated
.ListColumns("Avg-Smoke Density Pass Value (Ds)") = Me.ComboBoxSDpass.Value
end With
End Sub



I would really appreciate any help!

[Kenneth Hobs]

Where do you expect to find SampleID? If you want to search by column 1, this shows how. It shows how to get all column 1 listobject tables in one sheet but the concept is the same.

Sub Test_ASListObjectsByColumn()
Dim r As Range
Set r = ASListOjbectsByColumn(1)
MsgBox r.Cells.Count
MsgBox r.Address
End Sub


Function ASListOjbectsByColumn(lCol As Integer, Optional aSheet As Worksheet = Nothing) As Range
Dim r As Range, rr As Range, i As Integer, ii As Integer
If aSheet Is Nothing Then Set aSheet = ActiveSheet
i = aSheet.ListObjects.Count
Select Case i
Case 0
Set ASListOjbectsByColumn = Nothing
Exit Function
Case 1
Set rr = aSheet.ListObjects(1).ListColumns(lCol).Range
Set r = rr.Offset(1).Resize(rr.Rows.Count - 1)
Set ASListOjbectsByColumn = r
Case Else
Set rr = aSheet.ListObjects(1).ListColumns(lCol).Range
Set r = rr.Offset(1).Resize(rr.Rows.Count - 1)
For ii = 2 To i
Set rr = aSheet.ListObjects(ii).ListColumns(lCol).Range
Set r = Union(r, rr.Offset(1).Resize(rr.Rows.Count - 1))
Next ii
End Select
Set ASListOjbectsByColumn = r
End Function

[Newton/sqm]

Thank you for your fast reply. I will implement that into my code.