RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 788
- Office Version
- 2010
- Platform
- Windows
Hi all, I have a SPLIT custom function that takes a comma separated list of values and breaks them down, look at the following:
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
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.
VBA Code:
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
VBA Code:
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.
Last edited: