Vlookup and sum from multiple sheet

[sadath]

Hi

what is wrong in the UDF

Function MLookup(LV As String, Rng As Range, Col As Varient, ShName As Variant) As Integer
Dim Ans As Integer

For Each Cl In ShName

Ans = Ans + Evaluate("=SUM(VLOOKUP(" & LV & "," & Cl & "!" & Rng & "," & Col & ",0))")
Next Cl
MLookup = Ans

End Function

syntax used =MLookup(10,A5:Z100,{3,4,5},SHNAME) where SHNAME is sheet name

my aim is to add values from multiple sheet using the array formula =SUM(VLOOKUP(10,A5:Z100,{3,4,5},0) where A5:Z100 will change for multiple sheet, eg Sheet1!A5:Z100 , Sheet2!A5:Z100,Sheet3!A5:Z100 etc

 

[James006]

Hi,

Col As Varient ...

Is it just a typo ...?