Index Match in VBA for Ranges (finding a loan bala

I was trying to implement the index match combination in VBA to find a number in a range given 2 conditions. The below seems like a great approach, however, my inputs do not come from excel but from a variable that changes in the code itself. For the life of me I can't figure it out but I am a newbie.

Excel / VBA - Index Match function using Dynamic Ranges

What happens if your name and date instead are a loan number (1,2,3,etc) and date (6/30/2013) and are not in a spreadsheet but are generated in the VBA Code so that then the code can go to a range and look for the balance of that loan in such date and store it to a variable

-----------------RANGE DEFINITIONS-------------------------------------------------------------------------

About the code: Cantidad, ID and Fecha are dymanic ranges defined in the following way:

With Worksheets("CFs")
Set ID = Range("offset($a$3,4,0,counta($A:$A)-4,1)")
Set Fecha = Range("offset($b$3,4,0,counta($B:$B)-4,1)")
Set Cantidad = Range("offset($f$3,4,0,counta($F:$F)-4,1)")
End With

------------------FUNCTION CODE---------------------------------------------------------------------- about the function : dia1 and ID are a date that changes monthly and a loan number that loops one a time until the total number of loans are reached.

Public Function TestIndexMatch1(ByRef Cantidad As Range, _
                                                    ByRef Prestamo As Integer, _
                                                    ByRef Dia1 As Date, _
                                                    ByRef ID As Range, _
                                                    ByRef Fecha As Range)

                    Const Template As String = "=INDEX({0},MATCH(1,({1}={2})*({3}={4},{5}))"

                    Const MATCH_TYPE = 0
                    On Error GoTo Err_Handler
                    Err.Number = 0

                    Dim originalReferenceStyle
                    originalReferenceStyle = Application.ReferenceStyle
                    Application.ReferenceStyle = xlR1C1

                    Dim myFormula As String
                    myFormula = Replace(Template, "{0}", Cantidad.Address())
                    myFormula = Replace(Template, "{1}", Prestamo.Address())
                    myFormula = Replace(Template, "{2}", Dia1.Address())
                    myFormula = Replace(Template, "{3}", ID.Address())
                    myFormula = Replace(Template, "{4}", Fecha.Address())

                    TestIndexMatch1 = Application.Evaluate(myFormula)

Err_Handler:
                        If (Err.Number <> 0) Then MsgBox Err.Description
                        Application.ReferenceStyle = originalReferenceStyle


End Function

First off it looks like you are missing a few things:

a closing bracket in the formula string ...{4}),{5}...
a dot before range in your name definitions (Set ID = .Range)

But i think you might want to go about things a little differently. If you are defining variables in VBA to be used in worksheet functions on the sheet, you need to be careful about defining dependencies. Each range that is used in the VBA function should be an input argument to the function so Excel will recalculate whenever that value changes. Another issue with evaluating formulas in code is there is a lack of intermediate debug information and functions don't always evaluate as they would on the sheet.

For the first part I think you want to setup sheet names, this can be done by making the following adjustment to your code above:

Sub SetUpNames()
With Worksheets("CFs").Names
    .Add "ID", "=offset($a$3,4,0,counta($A:$A)-4,1)"
    .Add "Fecha", "=offset($b$3,4,0,counta($B:$B)-4,1)"
    .Add "Cantidad", "=offset($f$3,4,0,counta($F:$F)-4,1)"
End With
End Sub

This could also be done through the defined name dialog box. These names can then be inserted into the function in the linked post.

For the second section of code, here is an alternative to the function in the linked post which also uses a worksheet function approach:

Public Function TestIndexMatch2(ByRef outputRange As Range, _
                            ByRef nameCriteria As Range, _
                            ByRef dateCriteria As Range, _
                            ByRef nameRange As Range, _
                            ByRef dateRange As Range)

Dim v as Variant
With Application
    v = .CountIfs(nameCriteria, nameRange, dateCriteria, dateRange)
    TestIndexMatch2 = .Index(outputRange, .Match(1, v, 0))
End With

End Function

(Now at least the watch window is available to evaluate intermediate results if required).

Note: Using Application without .WorksheetFunction returns a variant which allows for arrays in arguments and results. However VBA doesn't expose all worksheet functions or Excel operators so you need to be more resourceful with this approach, for example the Exact function is missing but there are workarounds like len(substitute(a,b,""))=0.