VBA custom function is getting a correct result, but not applying to cell

[RockandGrohl]

Hi all, I have a SPLIT custom function that takes a comma separated list of values and breaks them down, look at the following:

Function CoachSTR(cl As Range) As String
   Dim Sp As Variant
   Dim i As Long
   Sp = Split(cl, ", ")
   For i = 0 To UBound(Sp)
      If InStr(1, Sp(i), "Flying", vbTextCompare) = 0 And InStr(1, Sp(i), "(RS)", vbTextCompare) = 0 Then
         CoachSTR = CoachSTR & Sp(i) & ", "
      End If
   Next i
   CoachSTR = Left(CoachSTR, Len(CoachSTR) - 2)
End Function

What this does is take a string like "Blackpool,Flying from Manchester (MAN),(RS) Preston" and just returns "Blackpool"

If I step through the code, it looks at all three items within the commas and correctly returns "Blackpool"

However, the result of

temp.Range("B3").FormulaR1C1 = "=(coachSTR(R2C2))"

is "0"

This worked ONCE tonight in a loop of over 500 instances, for every other instance, the result of the formula stubbornly remains as 0, even though looping through gives the correct result.

Any ideas why? This has been working perfectly for over a year and is ran weekly. To my knowledge, nothing has changed.

I have tried changing the parameter of Split from ", " to ","

And also tried changing the Left from -2 to -1

Cheers.


EDIT: Think I solved it. When the CoachSTR function was applied, the result of the formula was 0 for some reason. I stopped the macro and hit enter and it applied Blackpool. There is clearly some kind of bug preventing calculation as I have my Excel calc set to Auto. I added a new line "Application.Calculation xlCalculationAutomatic" and it appears to be working as intended. F'ing excel.

 

[Yongle]

Does this function return the correct string

Function CoachSTR(cl As Range) As String
    Dim txt As String: txt = cl.Text
    If LCase(txt) Like "*,*flying*(rs)*" Then CoachSTR = Trim(Split(txt, ",")(0))
End Function

temp.Range("B3").Formula = "=coachSTR(R2C2)"

 

[RockandGrohl]

I will try this thanks as it looks much easier to troubleshoot. Will have to look on Thursday/Friday, cheers!