Home

Index/Match in vba code

%3CLINGO-SUB%20id%3D%22lingo-sub-751782%22%20slang%3D%22en-US%22%3EIndex%2FMatch%20in%20vba%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-751782%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20need%20to%20know%20the%20index%20number%20for%20a%20certain%20name%2C%20for%20example%20when%20I%20write%20in%20TextBox1%20%3D%20Sara%2C%20and%20click%20the%20button%2C%20Then%20TextBox2%20should%20return%20index%20value%20opposite%20to%20that%20name%20in%20the%20table%20(Table1)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20344px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123121i37FD35BD7A4EB83D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22445.PNG%22%20title%3D%22445.PNG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EUserForm2%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20288px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123122iD9F5364D0C8B73FE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Table1.PNG%22%20title%3D%22Table1.PNG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ETable1%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ethe%20code%20I%20tried%20below%20from%20someone%20earlier%2C%20but%20it%20says%3A%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%22compile%20error%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMethod%20or%20data%20member%20not%20found%22.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWhat%20did%20I%20do%20wrong%3F%3F%3F%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ethe%20code%3A%3C%2FSPAN%3E%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20CommandButton1_Click()%3CBR%20%2F%3EDim%20ListObject%20As%20Range%3CBR%20%2F%3EDim%20tbl%20As%20ListObject%3CBR%20%2F%3ESet%20tbl%20%3D%20Sheet1.ListObjects(1)%3CBR%20%2F%3EDim%20MatchedRowNumber%20As%20Double%3CBR%20%2F%3EOn%20Error%20Resume%20Next%20'hide%20the%20exception%3CBR%20%2F%3EMatchedRowNumber%20%3D%20Application.WorksheetFunction.Match(TextBox1.Value%2C%20tbl.ListColumns(1)%2C%200)%3CBR%20%2F%3EOn%20Error%20GoTo%200%20'always%20directly%20re-activate%20error%20reporting!!!%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20MatchedRowNumber%20%26gt%3B%200%20Then%20'the%20row%20number%20will%20be%200%20if%20nothing%20matched%3CBR%20%2F%3EDim%20LookupValue%20As%20String%3CBR%20%2F%3ELookupValue%20%3D%20Application.WorksheetFunction.Index(Sheet1.tbl.ListColumns(1)%2C%20MatchedRowNumber%2C%201)%3CBR%20%2F%3ETextBox2.Value%20%3D%20LookupValue%3CBR%20%2F%3EElse%3CBR%20%2F%3EMsgBox%20%22No%20match!%22%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-751782%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-751813%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2FMatch%20in%20vba%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-751813%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375284%22%20target%3D%22_blank%22%3E%40AvengerArts%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20managed%20it%20with%20the%20following%20code.%20See%20attached%20file%2C%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20TextBox1_Change()%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20rngCol%20As%20Range%3CBR%20%2F%3EDim%20rngCell%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20rngCol%20%3D%20Tabelle1.Range(%22Tabelle1%5BName%5D%22)%3CBR%20%2F%3ErngCol.Interior.ColorIndex%20%3D%20xlNone%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20rngCell%20In%20rngCol%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20rngCell.Value%20%3D%20Me.TextBox1.Value%20Then%3CBR%20%2F%3EMe.TextBox2.Value%20%3D%20rngCell.Offset(0%2C%20-1).Value%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20rngCell%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20title%3D%22VBA%20database%22%20href%3D%22https%3A%2F%2Fvba-tanker.com%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EThe%20VBA-Tanker%20-%20a%20database%20full%20of%20usefull%20macros%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-752094%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2FMatch%20in%20vba%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-752094%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20I%20was%20hoping%20to%20use%20the%20method%20I%20provided%2C%20but%20you%20made%20it%20simple%20though%20I%20tried%20to%20apply%20it%20but%20I%20get%20an%20error!%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20391px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123135i8E299321E7C6A03C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22445.PNG%22%20title%3D%22445.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20531px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123136i6B649F199D6E4117%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Table1.PNG%22%20title%3D%22Table1.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecould%20you%20help%3F%3C%2FP%3E%3CP%3EI'm%20not%20a%20programmer%20but%20I'm%20trying%20to%20learn!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-752110%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2FMatch%20in%20vba%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-752110%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375284%22%20target%3D%22_blank%22%3E%40AvengerArts%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20have%20to%20take%20a%20look%20at%20the%20name%20of%20the%20listobject.%20In%20my%20version%20it's%20%22Tabelle1%22%2C%20bit%20i%20am%20german...%20if%20you%20want%20you%20can%20send%20me%20an%20email%20with%20attachment%20to%20b.held%40held-office.de%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20put%20the%20macro%20in%20the%20file.%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3C%2FLINGO-BODY%3E
AvengerArts
New 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)

445.PNGUserForm2

Table1.PNGTable1

 

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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
7 Replies