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]

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

will refer to whatever sheet is active when the function calculates. I'm guessing that isn't the one you intended to refer to, but it isn't really clear which sheet you do mean.

 

[Rolly_Sefu]

The sheet names are from 1 to 15, this is where i need this line "Range("F" & arr(atext) + 16)"



And the sheet name where i gadder the info is called "Board".



I want the function to only evaluate for the sheets from 1 to 15

Any ideas ?

 

[RoryA]

That doesn't really help. When the function is called, there is nothing to indicate which sheet it should refer to, so how should the code know which one to use? Is it the one that the function is on, or a different one?

 

[Rolly_Sefu]

i placed the function in a module
and use the function a range of cells on the sheets names from 1 to 15 (=IF(LEN(J46)>2;checkifclosed(J46);J46))

even if i am in sheet 2 and switch to sheet 3 i can see the cell recalculating ! and this is what i want to stop

 

[RoryA]

That function should only recalculate if J46 recalculates.

But you still haven't explained which sheet it refers to.

 

[Rolly_Sefu]

The function is ment for the active sheet.

i noticed that something was wrong, when i made a vlookup from one of the sheets ( 1 - 15 )
On the board sheet ( an overview for all the other sheets ) when i hit F2 + enter => vlookup returs an error.
Then i go to the sheet 2 it recalculated the specific cell range => back to sheet board, vlookup - no error
F2 + enter => error again.

 

[RoryA]

The function already refers to the active sheet. Do you mean the sheet that the function is on? (which will not necessarily be active when it calculates)

 

[Rolly_Sefu]

The function is in a module
on sheet 3:
cell: O17=IF(LEN(J17)>2;checkifclosed(J17);J17)
copyed down to O61
on sheet board
Cell: A1=VLOOKUP("Overdue";'3'!O17:O61;1;FALSE)
start:
i go to sheet 3 function evaluates
i go to sheet board vlookup OK. if i hit F2+Enter => vlookup #N/A
and i repeat the steps from start:
I do not know how else i can explain this.

 

[Rolly_Sefu]

I made this modification.

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 = 29 'x
End Function

And it fixed my problem. but i still need the function to check that range

Is it possible to write this part of the code in another way but still to get the same result
The text that the function is supposed to work on is "26,27,28,29" and i need it to check all the 4 rows to check if they are <> "", if all are different that it will return the last result (29).