Help With Match And Index Vba Please

[Vodada]

Hi all,
I am trying to write a vba code that attempts to index and match between 2 spreadsheet and finally loop through rows and columns to input the match data.
Unfortunately nothings happens and no error by visual basic.
Appreciate if someone can help.

For info. the following are named columns inside the "Database" worksheet.
"Leavecode" , "Employeecode" , "M:AG" , "crew"

>>> use code - tags <<<

Sub LookupLeaveCodes()
'Declare variables
Dim wsData As Worksheet
Dim wsOutput As Worksheet
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long
Dim j As Long
Dim empCode As Variant
Dim leaveCode As Variant

'Set worksheet variables
Set wsData = ThisWorkbook.Worksheets("Database")
Set wsOutput = ThisWorkbook.Worksheets("Output")

'Find last row and column of data in worksheet
LastRow = wsOutput.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = wsOutput.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

'Loop through each row in the specified range
For i = 18 To LastRow
'Loop through each column in the specified range
For j = 25 To LastCol
'Check if the cell is empty
If Not IsEmpty(wsOutput.Cells(i, j).Value) Then
'Get the employee code and leave code for the current cell
empCode = wsOutput.Cells(i, 1).Value
leaveCode = Application.Index(wsData.Range("Leavecode"), _
Application.Match(1, (wsData.Range("Employeecode") = empCode) * (wsData.Range("M:AG") = wsOutput.Cells(17, j).Value) * (wsData.Range("crew") = wsOutput.Cells(14, j).Value), 0))
'Check if a leave code was found
If Not IsError(leaveCode) Then
'Populate the leave code into the current cell
wsOutput.Cells(i, j).Value = leaveCode
Else
'If no leave code was found, clear the current cell
wsOutput.Cells(i, j).ClearContents
End If
End If
Next j
Next i
'Inform user that the code has finished running
MsgBox "Leave code lookup complete.", vbInformation
End Sub

 

[BobBridges]

We can probably look close and figure out what's going wrong, but even before doing that here's something important for you to know: You can step through the program, one statement at a time, and see exactly what it's going at every point! No guessing needed.

Try this: Insert a "Stop" statement somewhere in the program, near the beginning. When you run the program again, it'll stop at that point and wait for you to say what to do; the Stop statement is highlighted yellow. Now check out the buttons that are listed under the Debug option in the VB Editor's ribbon. If you hit <F8>, for example, it executes the highlighted step and goes on to the next one. You can mouseover any scalar variable and a popup will show you that variable's value. You can right-click on the name of an object variable in your code, and select "Add Watch", and in the Watch window it'll show you the various properties of that object. You can keep stepping through your program and confirm exactly what is happening at each point. This usually (almost always) tells you exactly what's going wrong.

Mostly at some point when I do this I smack my forehead, say "oh, of course!" and fix the problem. Rarely I can't figure out why the program is responding that way and have to ask a question. In either case, the most valuable thing you can learn at this early stage is how to debug your programs in this stepwise way.

 

[Vodada]

We can probably look close and figure out what's going wrong, but even before doing that here's something important for you to know: You can step through the program, one statement at a time, and see exactly what it's going at every point! No guessing needed.

Try this: Insert a "Stop" statement somewhere in the program, near the beginning. When you run the program again, it'll stop at that point and wait for you to say what to do; the Stop statement is highlighted yellow. Now check out the buttons that are listed under the Debug option in the VB Editor's ribbon. If you hit <F8>, for example, it executes the highlighted step and goes on to the next one. You can mouseover any scalar variable and a popup will show you that variable's value. You can right-click on the name of an object variable in your code, and select "Add Watch", and in the Watch window it'll show you the various properties of that object. You can keep stepping through your program and confirm exactly what is happening at each point. This usually (almost always) tells you exactly what's going wrong.

Mostly at some point when I do this I smack my forehead, say "oh, of course!" and fix the problem. Rarely I can't figure out why the program is responding that way and have to ask a question. In either case, the most valuable thing you can learn at this early stage is how to debug your programs in this stepwise way.

 

[Vodada]

Thank you for replying bob and also the good tips which I will follow and polish up my skills.

 

[BobBridges]

Of course, once you've looked using the debugging tools to see what your program is doing, if you still have questions be sure to ask. I'm not just dumping you out there all alone.