代码之家 › 专栏 › 技术社区 › maxim465

引用UDF中的特定单元格

user-defined-functions for-loop excel-vba vba

1

maxim465 · 技术社区 · 6 年前

发生错误 #Value! .

我想,给这个牢房设置通行证的问题 ThisWorkbook.ThisWorksheet . 如何胜任?

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

1 回复 | 直到 6 年前

1

2

QHarr 6 年前

对的。也许你的意思是活动桌?

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

如果仅在工作表中将其用作自定义项,则删除工作表引用:

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