Index/Match in vba code

Copper Contributor

Hello, 

I need to know the index number for a certain name, for example when I write in TextBox1 = Sara, and click the button, Then TextBox2 should return index value opposite to that name in the table (Table1)

UserForm2UserForm2

Table1Table1

 

the code I tried below from someone earlier, but it says: 

"compile error

Method or data member not found".

What did I do wrong????

the code:

Private Sub CommandButton1_Click()
Dim ListObject As Range
Dim tbl As ListObject
Set tbl = Sheet1.ListObjects(1)
Dim MatchedRowNumber As Double
On Error Resume Next 'hide the exception
MatchedRowNumber = Application.WorksheetFunction.Match(TextBox1.Value, tbl.ListColumns(1), 0)
On Error GoTo 0 'always directly re-activate error reporting!!!

If MatchedRowNumber > 0 Then 'the row number will be 0 if nothing matched
Dim LookupValue As String
LookupValue = Application.WorksheetFunction.Index(Sheet1.tbl.ListColumns(1), MatchedRowNumber, 1)
TextBox2.Value = LookupValue
Else
MsgBox "No match!"
End If
End Sub
3 Replies

@AvengerArts 

Hi, 

i managed it with the following code. See attached file, too.

 

Private Sub TextBox1_Change()

Dim rngCol As Range
Dim rngCell As Range

Set rngCol = Tabelle1.Range("Tabelle1[Name]")
rngCol.Interior.ColorIndex = xlNone

For Each rngCell In rngCol

If rngCell.Value = Me.TextBox1.Value Then
Me.TextBox2.Value = rngCell.Offset(0, -1).Value
End If

Next rngCell

End Sub

 

Best regards

Bernd

The VBA-Tanker - a database full of usefull macros

 

@Berndvbatanker 

Thanks, I was hoping to use the method I provided, but you made it simple though I tried to apply it but I get an error! 

445.PNGTable1.PNG

 

 

could you help?

I'm not a programmer but I'm trying to learn!

@AvengerArts 

You have to take a look at the name of the listobject. In my version it's "Tabelle1", bit i am german... if you want you can send me an email with attachment to b.held@held-office.de 

i put the macro in the file.

Best regards

Bernd