VBA

[Peter Quill]

Best Regards.

 

[561414]

Provide a sample database and explain how the info looks now and how you want it to look. That way we can provide a solution or a better approach to this problem.

 

[Peter Quill]

Provide a sample database and explain how the info looks now and how you want it to look. That way we can provide a solution or a better approach to this problem.

Hello Edgar,

Edited & corrected as you suggested. But I am not sure you can share an Excel file here. That's why I had attached images. Let me know if you need more information regarding this.
Regards.

 

[561414]

You have 10 posts now, you should be able to attach it now.

This is your code with 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

 

[Peter Quill]

You have 10 posts now, you should be able to attach it now.

Here is the Excel file. Thank you.

 

[561414]

Still no attachment, Peter. Something went wrong?

 

[Gasman]

You cannot attach Excel files.
You will need to rename the extension to .txt or some extension that is permitted, else zip it.

 

[561414]

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"

This formula will match Y14, Y17 and A18 ("Output") with L:L, M:AG and D: D ("Database") and return value of K:K ("Database"). Inside "Output" worksheet, I will then drag the formula in cell Y18 across to AG120.
I am hoping the VBA code loops through rows 18 to the last non-empty row in column Y, and loops through columns Y to DD until the last non-empty cell in that row.

Ahh gotcha. The comments in your code show what you want to do.

So, you want to type the leaves in your database worksheet and then you want them to appear on your output range that's inside your output worksheet. Let me see.

 

[Peter Quill]

You cannot attach Excel files.
You will need to rename the extension to .txt or some extension that is permitted, else zip it.

Here is the zipped file. Let me know if you need further information regarding this. Thank you for your kind responses.

 

[561414]

What is the role of the crew in the two tables?

 

[arnelgp]

i used ADODB.Recordset.
see if it is faster than using formula.
click on the "Recalculate" button.

 

[Peter Quill]

i used ADODB.Recordset.
see if it is faster than using formula.
click on the "Recalculate" button.

It worked perfectly. Thanks to you