User Defined Function Returning #Value

Question

I have come across a situation which required me to average the result of an array of Vlookups. I had no idea how to achieve this with formulas and it seemed like nobody else on StackOverflow had any idea either.

So I decided to write a function to do the job for me. Unfortunately it returns the "#VALUE!" error and I have no idea why! The function works fine when tested with a msgbox. I have annotated my code below:

Option Explicit

Public Function AvgVlookup(Target_Array As String, Lookup_Array As String, Column_Index As Long) As Double

Dim Result As Double
Dim Total As Double
Dim Counter As Long
Dim TargetRange As Range
Dim LookupRange As Range
Dim Cell As Range

' Remove Absolute Indicator
Target_Array = Replace(Target_Array, "$", "")
Lookup_Array = Replace(Lookup_Array, "$", "")

' Convert String to Range
Set TargetRange = Range(Left(Target_Array, InStr(1, Target_Array, ":") - 1), Mid(Target_Array, InStr(1, Target_Array, ":") + 1))
Set LookupRange = Range(Left(Lookup_Array, InStr(1, Lookup_Array, ":") - 1), Mid(Lookup_Array, InStr(1, Lookup_Array, ":") + 1))

' Set Variables to 0
Counter = 0
Total = 0

' For each cell in defined array
For Each Cell In TargetRange

' Vlookup the cell and save lookup value to Result variable
    Result = Application.WorksheetFunction.vlookup(Cell, LookupRange, Column_Index, "False")

' Update variables used to calculate average
    Total = Total + Result
    Counter = Counter + 1

Next Cell

' Perform calculation
AvgVlookup = Total / Counter

End Function

---

Sub test()

MsgBox AvgVlookup("A5:A8", "G5:H8", 2)

End Sub

Any ideas?

Thanks!

Answer 1

Two things:

First, the way you are setting your ranges are a little long, it can be truncated to simply:

Set TargetRange = Range(Target_Array)

No need to parse the strings after removing the $.

Second, you need to put in an error check in case one of the values in the target range is not in the lookup range.

The whole code:

Public Function AvgVlookup(Target_Array As String, Lookup_Array As String, Column_Index As Long) As Double


Dim Total As Double
Dim Counter As Long
Dim TargetRange As Range
Dim LookupRange As Range
Dim Cell As Range

' Remove Absolute Indicator
Target_Array = Replace(Target_Array, "$", "")
Lookup_Array = Replace(Lookup_Array, "$", "")

' Convert String to Range
Set TargetRange = Range(Target_Array)
Set LookupRange = Range(Lookup_Array)

' Set Variables to 0
Counter = 0
Total = 0

' For each cell in defined array
For Each Cell In TargetRange

' Vlookup the cell and save lookup value to Result variable
    Dim Result
    Result = Application.VLookup(Cell, LookupRange, Column_Index, "False")
    If IsNumeric(Result) Then
        Total = Total + Result
        Counter = Counter + 1
    End If


Next Cell

' Perform calculation
AvgVlookup = Total / Counter

End Function

With the above function to call from the worksheet you would need to call it like this: =AvgVlookup("A5:A8", "G5:H8", 2)

But that is not very helpful. If you change your inputs to ranges:

Public Function AvgVlookup(TargetRange As Range, LookupRange As Range, Column_Index As Long) As Double

Dim Result As Double
Dim Total As Double
Dim Counter As Long
Dim Cell As Range


' Set Variables to 0
Counter = 0
Total = 0

' For each cell in defined array
For Each Cell In TargetRange

' Vlookup the cell and save lookup value to Result variable
    Dim t
    t = Application.VLookup(Cell, LookupRange, Column_Index, "False")
    If IsNumeric(t) Then
        Total = Total + t
        Counter = Counter + 1
    End If


Next Cell

' Perform calculation
AvgVlookup = Total / Counter

End Function

Then you would call it simply, =AvgVlookup($A$5:$A$8,$G$5:$H$8,2). This way you can just highlight the correct ranges and it will work. Also less typing trying to convert a string to a range when what you want to enter is a range.