Huge formula and Excel Autofill cell reference problem

[nicnad]

I have a function that is basically a vlookup that look for multiple criterias on multiple columns and that return in one cell all the values that match those criterias separated by commas. The function is the following :

Function VLookUpsog(ByRef rng As Range, ByVal Criteria1 As String _
    , ByVal refCol As Long, ParamArray a())
    Dim r As Range, i As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    With rng
        For Each r In rng.Columns(1).Cells
            If UCase(r.Value) = UCase(Criteria1) Then
                If IsMissing(a) Then
                    VLookUpsog = VLookUpsog & "," & r(, refCol).Value
                Else
                    For i = 0 To UBound(a) Step 2
                        If a(i) Like "<>*" Then
                            If Not UCase(r(, a(i + 1)).Value) Like UCase(Replace(a(i), "<>", "")) Then
                                dic(r(, refCol).Value) = Empty
                            End If
                        Else
                            If UCase(r(, a(i + 1)).Value) Like UCase(a(i)) Then
                                dic(r(, refCol).Value) = Empty
                            End If
                        End If
                    Next
                End If
            End If
        Next
    End With
    If dic.Count > 0 Then
        VLookUpsog = Join$(dic.Keys, ", ")
    Else
        VLookUpsog = CVErr(xlErrNA)
    End If
    Set dic = Nothing
End Function

I have been using this function for a while without any problem. Now I wrote a formula that count the number of comma and return me the last item. The formula is the following :
<!-- BEGIN TEMPLATE: bbcode_code -->


=IF(LEN(Vlookups('Sheet1'!$D$2:$K$996;Sheet2!A2;6;Sheet2!C2;3))-LEN(SUBSTITUTE(Vlookups('Sheet1'!$D$2:$K$996;Sheet2!A2;6;Sheet2!C2;3);",";""))=0;Vlookups('Sheet1'!$D$2:$K$996;Sheet2!A2;6;Sheet2!C2;3);TRIM(MID(Vlookups('Sheet1'!$D$2:$K$996;Sheet2!A2;6;Sheet2!C2;3);FIND("^";SUBSTITUTE(Vlookups('Sheet1'!$D$2:$K$996;Sheet2!A2;6;Sheet2!C2;3)&",,";",";"^";(LEN(Vlookups('Sheet1'!$D$2:$K$996;Sheet2!A2;6;Sheet2!C2;3))-LEN(SUBSTITUTE(Vlookups('Sheet1'!$D$2:$K$996;Sheet2!A2;6;Sheet2!C2;3);",";"")))))+1;LEN(Vlookups('Sheet1'!$D$2:$K$996;Sheet2!A2;6;Sheet2!C2;3)))))



The problem is when I use autofill, the cell reference (A2 and C2), doesn't fill correctly. Instead of changing from A2,C2 to A3,C3 to A4,C4 they sunddenly go from A2,C2 to A4,C4 to A8,C8. The first autofill I make seems to have all the right data but as soon as recalculate the sheet or apply a filter all cells references are wrong.

I hope you will be able to help me with this one.

Thank you. <!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

 

[nicnad]

Any ideas?