so I have found a great answer on here showing how to get the values or strings between brackets (Get the value between the brackets) The code works perfectly, but when square brackets are involved, as in the following code, it gives me an error which I have been trying to fix for this whole day. I cannot seem to figure out what my problem is. The error notification I get is: "Run-time error '5': Invalid procedure call or argument"

For i = 6 To Worksheets(FileName).UsedRange.Columns.Count 

    kursName = Worksheets(FileName).Cells(1, i).Value

    klammerAuf = InStr(kursName, "[")
    klammerZu = InStr(kursName, "]")
    Cells(3, i) = Mid(kursName, klammerAuf   1, klammerZu - klammerAuf - 1)
Next i

CodePudding user response：

Okay, I might've found the solution. The selected cell didn't have a value with square brackets. The problem was that I didn't have any handling for errors. I'll leave this question for others who might run into the same error in the future. Man, I feel dumb. I'll take any propositions for error handling.

CodePudding user response：

Try this:

Function GetTextWithinBrackets(kursName As String, left As String, right As String)
    klammerAuf = InStr(kursName, left)
    klammerZu = InStr(klammerAuf   1, kursName, right) ' start searching for the closing bracket after the opening one
    If klammerAuf * klammerZu Then ' check if both brackets were found
        GetTextWithinBrackets = Mid(kursName, klammerAuf   1, klammerZu - klammerAuf - 1)
    Else
        GetTextWithinBrackets = "No " & left & " or " & right & " in the text"
    End If
End Function

Sub test()
    Debug.Print GetTextWithinBrackets("jkdsf]]]klsjc[=the text within brackets=]asd[[aw]]sd", "[", "]")
    Debug.Print GetTextWithinBrackets("872639281(=the text within brackets=)slsx(sladmlsa)", "(", ")")
    Debug.Print GetTextWithinBrackets("872639281=the text within brackets=]slsx(sladmlsa)", "[", "]")
End Sub

Outputs:

=the text within brackets=
=the text within brackets=
No [ or ] in the text