[RESOLVED]VBA-EXCEL How to work with R1C1

[Tobian]

My function matches the variable in cel R[1]C[-1] with variables in a matrix IsEen and gives back variable 2 when match is true and zero when match is wrong. But it gives me SyntaxError. Can somebody help me fix this function?

Function LookColumnBackAndRowFuther()
  
  If(16<>Type(MATCH(R[1]C[-1],IsEen,0)),2,0)
  
End Function

[Ecniv]

what is the function type?

[Tobian]

Type is an official function in Excel 97. For Example It gives as result 2 if is lyrics, 4 when is value, 16 = Error (like #n/b) or 64 when is matrix. I didn't make this function Type myself.
When value in r[1]c[-1] is not matching a value in matrix IsEen, the function match() gives back #n/b. Since I only have to know if values are matching and don't have to know which value that might be, I use the syntax "16<>Type()". And yes, I am a true VBA beginner...

[DKenny]

Tobian
In your code snippet here you have an IF statement without a THEN statement. - thats what's currently giving you trhe syntax error.

But if if you resolve that you still have an issue.
I assume your referring here to the "Type" worksheet function? If so then you have a problem, as this function is not exposed to VBA through the WorksheetFunction object.

What exactly do you want this function to achieve?

[Tobian]

I think You are right, the Type function is a worksheet-function. I first recorded a makro where I inserted the formula into a cel. Afterwards I made this function of it. Strange that the If-Function is not right.

OK, What I want the function to do:

I want the function to look in the cel, one column back and one row up (C[-1]R[1]) and match the value in this cel with a matrix. If there is a match, the function returns a "2", if there is no match the function returns a "0".

[DKenny]

When you say a matrix, do you mean a range of cells? If so would you want to pass the range to the function, or will it be the same range evey time you use the function?

[Tobian]

Yes, my matrix is a range of cells I selected in the worksheet and gave it a name. The values in it will not be changed. The values of the matrix must also be available for other functions.

[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

[DKenny]

I didn't catch that, sorry. I was assuming you would pass the cell you were checking to the function. Here's a version that doesn't need an input.

Function tobian() As Long
Dim LookupValue As Variant
Dim rngMatrix As Range
 
        Application.Volatile
        
        LookupValue = Application.Caller.Offset(1, -1).Value
        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 think we are almost there now.
VBA gives Error 424, Object nessessary for:
LookupValue = Application.Caller.Offset(1, -1).Value

[DKenny]

In debug mode can you type the following into the immediates window and let me knwo the result?

?Application.Caller.Address

[Tobian]

Can't find the immediates window, sorry. I am working with Dutch version and I am vba starter. Can you be more specific?

[DKenny]

OK, lets try some other questions.
What cell have you put the formula in when you get the error?
What is the range for your matrix?

[Tobian]

Ok, I used range B1:B4. In cell B1 in put the value 1, the formula I put in cell F11. Now I put the value 1 in cell E12. Function should give 2 now but instead gives 0. I get the error when I compile the function, that should be the reason.

[DKenny]

Hummm, I just did that and its working fine. Can you zip you excel file and upload it as an attachment?

Here's my sample.

[Tobian]

No problem, I'll try. But can it be because of my old 97 version, that it misses certain files? For instance it misses the VEnlLR3.hlp file, this is the vba help file which is not on the 97 installation disc. That's the file the problem solver refers to when I search for Error 424 , but it is not there.

[Tobian]

In debug mode can you type the following into the immediates window and let me knwo the result?

?Application.Caller.Address

I get Run-time Error 424 Object needed

[Tobian]

Oke, I resolved the bug. That means when I use following public function ValueOneBack, I don't get the error 424 anymore. But it doesn't make my function MatchOneBack work the way it has to. But that's another question..

Public Function ValueOneBack() As Variant
    
    ValueOneBack = ""
    
    Application.Volatile
    
    If TypeName(Application.Caller) = "Range" Then
        If Application.Caller.Row <> 65536 And Application.Caller.Column <> 1 Then
            ValueOneBack = Application.Caller.Offset(1, -1).Value
        End If
    End If
 
End Function
 
Function MatchOneBack() As Integer
    Dim LookupValue As Variant
    Dim rngMatrix As Range
    
    LookupValue = ValueOneBack()
    
    Set rngMatrix = Worksheets(1).Range("B1:B4")
        
    If rngMatrix.Find(What:=LookupValue, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        MatchCase:=False) Is Nothing Then
        MatchOneBack = 0
    Else
        MatchOneBack = 2
    End If
        
    Set rngMatrix = Nothing
    
End Function