دالة جمع بيانات من اوراق متعددة-[SUMIF across multiple sheets [UDF
 |
الكود المدخل فى المديول كالتالى
(Function SumifAMS(lookup_value As Range, ParamArray cellranges() As Variant
Dim i As Integer, rng1 As Variant, temp As Single, a As Boolean
Dim rng2 As Variant, value As Variant, j As Single
If (UBound(cellranges) + 1) Mod 2 <> 0 Then
MsgBox "The number of range arguments must be even. 2, 4 , 8 ... and so on"
Exit Function
End If
For i = LBound(cellranges) To UBound(cellranges) Step 2
If cellranges(i).Rows.Count <> cellranges(i + 1).Rows.Count Then
MsgBox "The number of rows in range arguments don´t match."
End If
If cellranges(i).Columns.Count <> 1 Then
MsgBox "Range arguments can only have size one column each."
Exit Function
End If
rng1 = cellranges(i).value
rng2 = cellranges(i + 1).value
For j = LBound(rng1) To UBound(rng1)
For Each value In lookup_value
If UCase(rng1(j, 1)) = UCase(value) Then a = True
Next value
If a = True Then temp = temp + rng2(j, 1)
a = False
Next j
Next i
SumifAMS = temp
End Function
ليست هناك تعليقات:
إرسال تعليق