Capturering Array Deta

[Priyantha]

Dear All,

I am using a macro to cal culate arrears and employees deta getting from other closed work book shee to a array "Employee_Deta". For Arrears format,
"Error - Increment & Arrears Format.xlsm" file and for deta sheet, "Employees Deta.xlsx" file are in same folder. Selected deta are in array but below code part not working correcly. I have attached Arrears format file and Deta sheet file

Code: Select all

matchDate1 = empNo & "-" & Format(startDate, "yyyy-mm")
 matchDate2 = empNo & "-" & Format(endDate, "yyyy-mm")
    
    ' Initialize matchRow variables
    matchRow1 = 0
    matchRow2 = 0
       
    ' Iterate through the Employee_Deta array to find matching rows
    For i = 2 To UBound(Employee_Deta, 2)
        If Employee_Deta(2, i) & "-" & Format(Employee_Deta(1, i), "yyyy-mm") = matchDate1 Then
            matchRow1 = i
        End If
        If Employee_Deta(2, i) & "-" & Format(Employee_Deta(1, i), "yyyy-mm") = matchDate2 Then
            matchRow2 = i
        End If
       
        If matchRow1 > 0 And matchRow2 > 0 Then Exit For
    Next i
    
    ' Transfer In or Active Employee Update
    If matchRow1 > 0 Then
        sal_01 = Employee_Deta(5, matchRow1)
    End If
        
    ' Check if sal_01 is an error or matchRow1 is 0
    If IsError(sal_01) Or matchRow1 = 0 Then
        With ws.Range("F1")
            .Value = "Transfer In or Out of Range"
            .Interior.Color = RGB(250, 100, 0)
        End With
    Else
        With ws.Range("F1")
            .Value = "Active Employee"
            .Interior.ThemeColor = xlThemeColorAccent3
        End With
    End If

please help me to correct this issue

Br,

Priyantha.

[HansV]

You write "below code part not working correcly". In what way?
Do you get an error? If so, what is the error message and which line causes it?
Or does the code produce an unexpected/undesirable result? If so, please provide details.

[Priyantha]

Dear Hans,

I try to get concerned emplyees salary for two variables, in to
matchRow1 - Start date Salary
matchRow2 - end date salary

if any amployee stat date salary = 0, that employee should, ws.Range("F1"). value = "Transfer In or Out of Range" & cloured =Interior.Color = RGB(250, 100, 0)
else
ws.Range("F1").Value = "Active Employee", cloured = Interior.ThemeColor = xlThemeColorAccent3

But, although employee deta (Belongs to start and end date) are in the deta sheet and in the array "Employee_Deta", not change correctly and always semms "Transfer In or Out of Range" & cloured =Interior.Color = RGB(250, 100, 0)

I used this methord in some places in my code them also not working correcly. i think same issue.

thanks,

Priyantha

[HansV]

You didn't take into account that GetRows is zero-based. So you have to:
- Initialize matchRow1 and matchRow2 to -1 instead of 0
- Loop from 0 instead of from 1 or 2.
- Check whether matchRow1 and matchRow2 are >=0 instead of >0.

Error - Increment & Arrears Format.xlsm

[Priyantha]

Dear Hans,

I is working correctly, Thank U soo much. Please give me a extra advice, There are atleast 100,000 rows of emplyee deta in My detabase sheet. while getting deta in to array, which methord is th fastest below?

01. Getting deta in to Array (Employee_Deta) from normal external closed workbook sheet or
02 Getting deta in to Array (Employee_Deta) from a table (normal external closed workbook sheet converted as table)

Br,

Priyantha.

[HansV]

I don't know - why don't you test both methods to see if there is a noticeable difference?