Cám ơn bạn
Sửa vào hàm của bạn thì không biết, nhưng viết hàm khác thì GPT làm ổn:
Function HamDoTim(lookupValue As Variant, criteriaValue1 As Variant, criteriaValue2 As Variant) As Variant
Dim ws As Worksheet
Dim i As Long
Dim lastMatch As Variant
' Xác d?nh Sheet ch?a d? li?u
On Error Resume Next
Set ws = ThisWorkbook.Sheets("THONG TIN CONG TY (BO SUNG)")
If ws Is Nothing Then
HamDoTim = CVErr(xlErrRef) ' L?i #REF! n?u không tìm th?y Sheet
Exit Function
End If
On Error GoTo 0
' M?c d?nh k?t qu? là l?i n?u không tìm th?y
lastMatch = CVErr(xlErrNA)
' L?p ngu?c t? dòng cu?i lên (gi?i h?n l?i 9999 n?u c?n)
For i = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row To 2 Step -1
If ws.Cells(i, 2).Value = lookupValue And _
IsNumeric(ws.Cells(i, 3).Value) And _
ws.Cells(i, 3).Value <= criteriaValue1 And _
ws.Cells(i, 4).Value = criteriaValue2 Then
lastMatch = ws.Cells(i, 5).Value
Exit For ' D?ng vòng l?p khi tìm th?y k?t qu?
End If
Next i
HamDoTim = lastMatch
End FunctionFunction DoTim(RngMaDonVi As Range, RngNgayThang As Range, RngTuTraCuu As Range) As Variant
Dim x$, s$, z$
If Application.UseSystemSeparators Then
s = Application.International(xlDecimalSeparator)
Else
s = Application.DecimalSeparator
End If
x = IIf(s = ",", ";", ",") '' -> Tách đối số , và ;
z = IIf(s = ",", "\", ",") '' -> Mảng {0\1\2\3;4\5\6\7} và {0,1,2,3;4,5,6,7}
Dim CongThuc As String
CongThuc = _
"=LOOKUP(1" & x & "1/" & _
"(B3:B9999=" & "'" & RngMaDonVi.Parent.Name & "'!" & RngMaDonVi.Address & ")/" & _
"(C3:C9999*1<=" & Replace(CDec(RngNgayThang.Value), ".", s) & ")/" & _
"(D3:D9999=" & "'" & RngTuTraCuu.Parent.Name & "'!" & RngTuTraCuu.Address & ")" & x & "" & _
"E3:E9999)"
'' Sai -> RngMaDonVi.Address "'" & RngMaDonVi.Parent.Name & "'!" & RngMaDonVi.Address
'' Sai -> RngNgayThang.Address
'' Sai -> RngTuTraCuu.Address
'' Sai -> B2:B9999 'Sheet'!B2:B9999
'' Sai -> C2:C9999
'' Sai -> D2:D9999
'' Sai -> E2:E9999
DoTim = Application.Evaluate(CongThuc)
'' Sai -> Application.Evaluate(CongThuc)
End Function
