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!

have you placed a break-line in the function and stepped through it line-by-line by to evaluate excactly where the error occurs? — Scott Holtzman
– Scott Holtzman, Commented Mar 15, 2016 at 14:29
Hi Scott. There is no error in the code itself. I can run it via the test sub and it will return exactly the result I want into the msgbox. It just returns this error in the cell I enter it into for some reason. — JaayB
– JaayB, Commented Mar 15, 2016 at 15:16
There is no error in the code itself ... and then ... just returns this error in the cell ... so there must be something off with the code if its not returning the value to the cell. If you place a break point in the code and then hit F2 in the cell where it's located you can step-through line-by-line to see why its returning the error message. But I think @ScottCraner has you taken care of :) — Scott Holtzman
– Scott Holtzman, Commented Mar 15, 2016 at 15:26

Scott Craner · Accepted Answer · 2016-03-15 15:26:00Z

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.

edited Mar 15, 2016 at 15:26

answered Mar 15, 2016 at 14:45

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JaayB Over a year ago

Hi Scott, your amendments work with the test sub I posted. Great to know I can shorten my code but it still produced the #Value! error when used in a worksheet. If this worked for you then I am not sure it why it would produce the error for me.

Scott Craner Over a year ago

With out seeing your data it is hard to tell. It does work for me. @IIIBarcodeIII

Scott Craner Over a year ago

Sorry you said you are using this in a worksheet? then we need to change a few things. Give me a second. @IIIBarcodeIII

JaayB Over a year ago

Thanks Scott, my workday is just about over. I need to take care of a few things before I go so I will test the amendments tomorrow and get back to you. Thanks for your help! It looks like i was on the right track, but over complicating it.

Collectives™ on Stack Overflow

User Defined Function Returning #Value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related