Forum Discussion
Index/Match in vba code
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)
UserForm2
Table1
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
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 SubBest regards
Bernd
https://vba-tanker.com/
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!
could you help?
I'm not a programmer but I'm trying to learn!
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
