Need a simpler way to program with IF/THEN Logic

[kdleaver1974]

First, let me thank everyone with their help with various issues of my VBA Program.

Second, this isn't a problem more an inquiry of how to make things "cleaner" in writing my code.

What I am trying to do, using IF/THEN, is determine if a variable does NOT have a certain value, and if it doesn't, then to find the condition true.

The variable is a SKU that has a "-" in it in either the 5th, 6th, and 7th Character. I want to search the SKU and if it does NOT find the "-" in the SKU then to have the IF/THEN condition be true.

The only way I seem to be able to do this is the following way:

        If Mid(rcdFindItemSKU, 6, 1) = "-" Or _
         Mid(rcdFindItemSKU, 7, 1) = "-" Or _
         Mid(rcdFindItemSKU, 8, 1) = "-" Then
         
         Else
         
            AddLetterTorcdFindItemSKU = ""
            KeyCode = vbKeyShift
                        
            Exit Sub
            
        End If

The original code was the following:

        If Mid(rcdFindItemSKU, 6, 1) <> "-" Or _
         Mid(rcdFindItemSKU, 7, 1) <> "-" Or _
         Mid(rcdFindItemSKU, 8, 1) <> "-" Then
         
            AddLetterTorcdFindItemSKU = ""
            KeyCode = vbKeyShift
                        
            Exit Sub
            
        End If

The problem with the above code is two of the conditions will always be TRUE because if the Dash is in character 6, then characters 7 and 8 will be TRUE.

Is there another way to write the logic so I don't to use the Else statement? Thanks.

Ken L

[davegri]

If you just need to determine if the string has a dash in it, use the instr function. Below is true if no dash is present.

If InStr(rcdFindItemSku, "-") = 0 Then

[xps35]

The original code is wrong because of the OR's; that must be AND's.

[Welshgasman]

You say 5th,6th or 7th character but are using 6, 7 or 8?

tt="xxxx---xxxxx"
? mid(tt,5,1)
-
? mid(tt,6,1)
-
? mid(tt,7,1)
-

I read your request as correctly having an OR, as the - could be in any of those 3 positions.?

I was thinking of a regex expression which are quite powerful.
As you would appear to need this quite often, that is the way I would go.

Here is what ChatGPT offered. I tested it and it works.

Function HyphenInPosition(ByVal txt As String) As Boolean
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    
    With regex
        .Pattern = "^.{4,6}-"
        .IgnoreCase = True
        .Global = False
    End With
    
    HyphenInPosition = regex.TEST(txt)
End Function
Sub TestRegex()
    Debug.Print HyphenInPosition("1234-abc")    ' True (hyphen at 5th char)
    Debug.Print HyphenInPosition("12345-abc")   ' True (hyphen at 6th char)
    Debug.Print HyphenInPosition("123456-abc")  ' True (hyphen at 7th char)
    Debug.Print HyphenInPosition("1234---56abc") ' True at them all
    Debug.Print HyphenInPosition("123456abc")
End Sub

[CJ_London]

I would go for

if not (Mid(rcdFindItemSKU, 6, 3) like “*-*”) then
AddLetterTorcdFindItemSKU = ""
KeyCode = vbKeyShift

Exit Sub

End If

[Welshgasman]

I think we need clarification as to what the SKU can possibly be?
As #5 would not process "xxxx5-7xxxxx" correctly.

My interpretation was a - in any of the mentioned postions?

The variable is a SKU that has a "-" in it in either the 5th, 6th, and 7th Character.

[CJ_London]

OP says they want to simplify the code, not that the code doesn’t work- so I’m simplifying the logic provided rather that interpreting the description

As #5 would not process "xxxx5-7xxxxx" correctly.

?not (Mid("xxxx5-7xxxxx", 6, 3) like "*-*")
False

works for me

as does this if the its chars 5-7 rather than 6 to 8

?not (Mid("xxxx5-7xxxxx", 5, 3) like "*-*")
False

[moke123]

Simplified for readability -

Function MySku(strIN As String, strMatch As String) As Boolean

    Select Case InStr(1, strIN, strMatch)

        Case 5, 6, 7
            MySku = False
            
        Case Else
            MySku = True
            
    End Select

End Function

[jojowhite]

you may also try:

If Instr(Mid$(rcdFindItemSKU, 5, 3), "-") <> 0 Then
' dash is in the sKu
Else
' dash not found
End If