Excel Function Error ???

[Rolly_Sefu]

Hello
I have created 2 functions


F1

Function special_txttonr(text As String)
Dim arr(), a As Long
    c = 0
For a = LBound(Split(text, ",")) To UBound(Split(text, ","))
  ReDim Preserve arr(a)
    arr(a) = CInt(Split(text, ",")(a))
    b = arr(a)
    x = WorksheetFunction.Max(b, c)
    c = x
Next a
special_txttonr = x
End Function

F2

Function checkifclosed(text As String)
    Dim arr(), atext As Long
    For atext = LBound(Split(text, ",")) To UBound(Split(text, ","))
      ReDim Preserve arr(atext)
        arr(atext) = CInt(Split(text, ",")(atext))
        If Range("F" & arr(atext) + 16) <> "" Then
            x = arr(atext)
        Else
            x = 0
            Exit For
        End If
    Next atext
checkifclosed = x
End Function

When I am on the sheet where the function are they bought evaluate perfectly.


But when i try to reference the cell ( in a lookup table from VLookup ) the F2 function shows "".


Does anyone have any idea why the F2 function it will not remain evaluated ?


Thanks

 

[RoryA]

I will try this one last time. This line:

Range("F" & arr(atext) + 16)

refers to a cell in column F of whatever sheet is active at the time the function calculates. I assume it should refer to a specific sheet, but which one?

 

[Rolly_Sefu]

The text it is splittling is "26,27,28,29" - witch is in the same sheet with the function ( bought in a cell )
It needs to check in the same sheet if row 26 + 16 is <> ""
So i suppose it is for the active sheet.
Yes it should only refer to sheets with the name from 1 to 15

 

[RoryA]

If it's the same sheet the function is entered in, you should use Application.Caller.Worksheet.Range and not just Range.

 

[Rolly_Sefu]

Yes.


It's exactly what i needed. ( Vlookup does not return error anymore )


Thank you. And sorry for all the messages i did not explin the problem correctly.

 

[RoryA]

That's OK - we got there in the end, which is the main thing!