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