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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
573
Office Version
  1. 2010
Platform
  1. 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:

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:

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Does this function return the correct string
VBA Code:
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

VBA Code:
temp.Range("B3").Formula = "=coachSTR(R2C2)"
 
Last edited:

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
573
Office Version
  1. 2010
Platform
  1. Windows
I will try this thanks as it looks much easier to troubleshoot. Will have to look on Thursday/Friday, cheers!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,719
Members
415,853
Latest member
Newlife72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top