Function returns #VALUE but works in immediate window

[Sanne97]

I am trying to make a function where, based on an article code, the funciton looks in a different sheet with matching codes and takes the right discount (5 columns to the right of the cell with the code).

Then I would like to return a specific value when the discount is set as "standaard".

Everything seems fine in the immediate window, but when I enter the formula in a cell, It returns #VALUE.

Here is the code:

Function Discount()

Application.Volatile True

Dim c As Variant
Dim cl As Range
Dim r As Integer
Dim a As String
r = ActiveCell.Row
a = Sheets(1).Cells(r, 3).Value


Sheets(2).Activate
    With Sheets(2).Range("B3:B40000")
        Set cl = Sheets(2).Range("B2:B10000").Find(a, LookAt:=xlWhole)
        If Not cl Is Nothing Then
            cl.Select
        End If
    End With


If ActiveCell.Offset(0, 5).Value = "standaard" Then
    c = 1
        End If

  Discount = c

End Function

 

[RoryA]

Welcome to the forum.

You can't select a cell in a UDF. What should the result be if the value isn't found, or if the cell 5 to the right doesn't contain "standaard"?

 

[Sanne97]

Thank you!

What i struggle with is that i need the value from "a", look for a matching value in sheet 2, then go five columns to the right and look at that value. If that value is "standaard", then it should refer to a different cell that contains the discount value.

 

[Sanne97]

I solved the issue with the following code:



Public Function Bruto() As Variant

Application.Volatile True

r = Application.Caller.Row
b = Cells(r, 20).Value

If WorksheetFunction.Index(Sheets(2).Range("G:G"), _
WorksheetFunction.Match(Sheets(1).Cells(r, 3).Value, Sheets(2).Range("B:B"), 0)) = "standaard" Then
Bruto = "ST"

ElseIf WorksheetFunction.Index(Sheets(2).Range("G:G"), _
WorksheetFunction.Match(Sheets(1).Cells(r, 3).Value, Sheets(2).Range("B:B"), 0)) <> "standaard" Then
Bruto = b * (1 - WorksheetFunction.Index(Sheets(2).Range("G:G"), _
WorksheetFunction.Match(Sheets(1).Cells(r, 3).Value, Sheets(2).Range("B:B"), 0)))
End If

End Function