CODEWITHSUNDEEP
vbaexcelfor-loopuser-defined-functions
maxim465
maxim465

Reputation: 195

Referring to a specific cell in UDF

UDF aim: Compare the value of the range with the specific cells on the same sheet.

Error occurs #Value!.

I think , that the problem in setting the pass to this cell ThisWorkbook.ThisWorksheet. How to do it competently?

Function Fav(Diapozon As Range) As Long
    Application.Volatile

    Dim n As Long

    For x = 1 To 4
        For y = 0 To 1
            If Diapozon.Value = ThisWorkbook.Thisworksheet.Cells(x + 29, y + 10).Value Or  _
               Diapozon.Offset(0, 1).Value = ThisWorkbook.Thisworksheet.Cells(x + 29, y + 10).Value Then
                n = 1
            End If
        Next y
    Next x

    Fav = n
End Function

Upvotes: 1

Views: 42

Answers (1)

QHarr
QHarr

Reputation: 84465

Correct. Perhaps you meant Activesheet?

Public Function Fav(ByVal Diapozon As Range) As Long
    Application.Volatile
    Dim n As Long, x As Long, y As Long
    For x = 1 To 4
        For y = 0 To 1
            If Diapozon.Value = ThisWorkbook.ActiveSheet.Cells(x + 29, y + 10).Value Or Diapozon.Offset(0, 1).Value = ThisWorkbook.ActiveSheet.Cells(x + 29, y + 10).Value Then
                n = 1
            End If
        Next y
    Next x
    Fav = n
End Function

If you are using this only in the sheet as an UDF then drop the sheet reference:

Public Function Fav(ByVal Diapozon As Range) As Long
    Application.Volatile
    Dim n As Long, x As Long, y As Long
    For x = 1 To 4
        For y = 0 To 1
            If Diapozon.Value = Cells(x + 29, y + 10).Value Or Diapozon.Offset(0, 1).Value = Cells(x + 29, y + 10).Value Then
                n = 1
            End If
        Next y
    Next x
    Fav = n
End Function

Upvotes: 2

Related Questions