[RESOLVED]VBA-EXCEL How to work with R1C1

[DKenny]

Try this. You will have to change the value of the rngMatrix variable to your named range.

Function tobian(ByVal LookupValue As Variant) As Long
Dim rngMatrix As Range
 
        Set rngMatrix = ThisWorkbook.Worksheets(1).Range("B4:C11")
        
        If rngMatrix.Find(What:=LookupValue, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            MatchCase:=False) Is Nothing Then
            tobian = 0
        Else
            tobian = 2
        End If
        
        Set rngMatrix = Nothing
End Function

[Tobian]

I tried the function tobian but it don't work the way I expected.
I changed the range of rngMatrix to mine. But I have to give the function a value to look for. There is no need for.
Example: when function Tobian is inserted in cell C3, I want the function to match the value of cell B4 with the rngmatrix and put the result "2" or "0" back in cell C3. When I insert the function in cell Y7, the function has to match the value in cell X8.
I tried to rebuild the function but the R[-1]C[1] gives SyntaxError.

Function tobian()
Dim rngMatrix As Range
 
        Set rngMatrix = ThisWorkbook.Worksheets(1).Range("B1:B5")
        
        If rngMatrix.Find(What:=R[-1]C[1], _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            MatchCase:=False) Is Nothing Then
            tobian = 0
        Else
            tobian = 2
        End If
        
        Set rngMatrix = Nothing
 
End Function