CODEWITHSUNDEEP
vbaexcel
Sandeep Thomas
Sandeep Thomas

Reputation: 4759

Formula Array returns nothing or just #VALUE! when using Evaluate

Im trying to evaluate Formula array to find a value from sheet with multiple criteria. The sheet name is Holiday_master

So in another sheet Temp_data I tried to exceute the following code to set that value in that cell using formula array

Public Function getCostMultiplier(dt As Date, wt As String) As Double   'dt is the date of entry, wt working time name "India Full Time","Singapore f........


        Dim lRow As Integer
        Dim we As Boolean
        we = IsWeekend(dt)
        Dim nhRange As Range
        Dim d As Double
        d = CDbl(dt)
        Dim location As String


        Select Case LCase(Trim(wt))
            Case "india full time 45"
                location = "INDIA"
            Case "singapore full time 40"
                location = "SINGAPORE"
            Case "uk full time 40"
                location = "UK"
        End Select

      Dim n As Integer
      'n = Application.Evaluate()
    lRow = Sheets("Holiday_master").Range("A1000").End(xlUp).Row + 1
    Dim formula As String
    Dim s As String
    s = Application.Evaluate("RC11")


    formula = printf("{=INDEX( Holiday_master!R2C3:R{0}C3,MATCH(1,(""{1}""=Holiday_master!R2C2:R{0}C2)*({2}=Holiday_master!R2C1:R{0}C1),0),1)}", lRow, location, d)
    ''''INDEX( Holiday_master!R2C3:R11C3,MATCH(1,("INDIA"=Holiday_master!R2C2:R11C2)*(43126=Holiday_master!R2C1:R11C1),0),1)
    n = Application.Evaluate(formula)

    getCostMultiplier = n

End Function


Public Function printf(mask As String, ParamArray tokens()) As String
Dim i As Long
For i = 0 To UBound(tokens)
    mask = Replace$(mask, "{" & i & "}", tokens(i))
Next
printf = mask
End Function

So in Temp_data sheet in a cell I set the formula as getCostMultiplier(RC11,RC4)', so obviously it reaches my function with parameters26-01-2018andINDIA`

So in the code we have a final formula which is I commented there INDEX( Holiday_master!R2C3:R11C3,MATCH(1,("INDIA"=Holiday_master!R2C2:R11C2)*(43101=Holiday_master!R2C1:R11C1),0),1) But its not evaluating as I expected or this could not be the way for evaluation of formula array.

If I execute that formula in that cell manually and on submitting (ctrl+shift+enter) it executes properly and returning the value.

So I didnt understand how to do that same from VBA or how to do that Evaluation. I never used Evaluation before

Upvotes: 2

Views: 231

Answers (2)

Sandeep Thomas
Sandeep Thomas

Reputation: 4759

I got it working with R1C1 itself. The issue was, I specified curly braces there in the formula array which is not required in the case of Evaluate

So here is my modified formula code with error validation also added

formula = printf("=IFERROR(INDEX(Holiday_master!R2C3:R{0}C3,MATCH(1,(""{1}""=Holiday_master!R2C2:R{0}C2)*({2}=Holiday_master!R2C1:R{0}C1),0),1),1)", lRow, location, CStr(d))

Upvotes: 2

user4039065
user4039065

Reputation:

Convert the formula from xlR1C1 to xlA1 syntax.

n = Application.Evaluate(Application.ConvertFormula(formula, xlR1C1, xlA1))

Using Evaluate within VBA should be done with xlA1 syntax.

Upvotes: 3

Related Questions