Still trying to figure out how to skip over code using Goto Statement

I still get an "invalid use of null" error on:

If AccountRef1 = "" Then
       AccountRefToUse = AccountRef2

It must be still executing this part even when one of the values is null

use "Exit Function" instead of "Goto EndOfSection" to exit inmediately
if the function dowes not exits you maybe forgetting something in the
main condition  "If IsNull([AccountRef1]) And IsNull([AccountRef2]) Then...."

I have reduced your function to just few lines. Could you test this for me?

Function FindDept(AccountRef1, AccountRef2) As String
    Dim colonPos As Integer
    AccountRefToUse As String, TextToRight As String

    If IsNull([AccountRef1]) And IsNull([AccountRef2]) Then Exit Function

    If IsNull([AccountRef1]) Then [AccountRefToUse] = [AccountRef2] Else [AccountRefToUse] = [AccountRef1]
    
    colonPos = InStr([AccountRefToUse], ":")
    
    TextToRight = Right([AccountRefToUse], Len([AccountRefToUse]) - colonPos)
    
    If colonPos = 0 Then FindDept = [AccountRefToUse] Else FindDept = TextToRight
End Function

Select allOpen in new window

Sid

Also one more thing. If there is no ":" in [AccountRefToUse] then you might incorrect results because of this line of code

colonPos = InStr([AccountRefToUse], ":")

If AccountRef1 and AccountRef2 are string then you can use this code as well.

Function FindDept(AccountRef1 As String, AccountRef2 As String) As String
    Dim colonPos As Integer
    AccountRefToUse As String, TextToRight As String

    If Len(Trim(AccountRef1)) = 0 And Len(Trim(AccountRef2)) = 0 Then Exit Function

    If Len(Trim(AccountRef1)) = 0 Then AccountRefToUse = AccountRef2 Else AccountRefToUse = AccountRef1
    
    colonPos = InStr(1, AccountRefToUse, ":")
    
    TextToRight = Right(AccountRefToUse, Len(AccountRefToUse) - colonPos)
    
    If colonPos = 0 Then FindDept = AccountRefToUse Else FindDept = TextToRight
End Function

Select allOpen in new window

Sid

Thanks to you both.
It was the exclusion of the "Or" that I was missing.  I'd actually thought of that before fraigor mentioned it.  I guess that means I'm learning.  LOL. But you guys did teach a few more things (Exit Function, using as, shortening the code)  Thank you both!

Oh, here is the final code that works:

 

Function FindDept(AccountRef1, AccountRef2)
Dim colonPos As Integer
Dim AccountRefToUse As String
Dim TextToRight As String

If (IsNull([AccountRef1]) Or [AccountRef1] = "") And (IsNull([AccountRef2]) Or [AccountRef2] = "") Then
    FindDept = ""
Exit Function
End If

If AccountRef1 = "" Then
   AccountRefToUse = AccountRef2
Else
   AccountRefToUse = AccountRef1
End If

colonPos = InStr([AccountRefToUse], ":")
TextToRight = Right([AccountRefToUse], Len([AccountRefToUse]) - colonPos)

If colonPos = 0 Then
FindDept = [AccountRefToUse]
Else
FindDept = TextToRight
End If
EndofSection:
End Function

Select allOpen in new window

BBlu: Please refer to ID: 35221406

Sid

Thanks, Guys

BBlu,

Glad you got your answer.  Just so you know, use of GoTo is very much discouraged, except for invoking an error handler.  The other Experts did not come out and say this, but do note how their suggestions avoid the need for GoTo by using the typical flow-of-control structures VBA provides.  As such, they are implicitly leading you away from GoTo :)

Patrick

Got it.  Thank you very much, Patrick.  Have a great weekend.