CODEWITHSUNDEEP
excelvba
Zanam
Zanam

Reputation: 4807

String split in excel VBA gives type mismatch error

I am working with Macro in excel VBA. I have a variable as follows:

debug.Print(Categories)
Tools & Home Improvement 
› 
Power & Hand Tools 
› 
Power Tool Parts & Accessories 
› 
Woodworking Project Plans & Kits 
› 
Woodworking Project Kits

I am trying to string split it as follows in the Immediate window but I get the error: "Run Type Error 13, Type Mismatch"

debug.Print(Split(Categories, ">"))

I am trying to extract only Tools & Home Improvement from Categories variable. Is there any other function that I can use for above.

Under Locals window, Categories is of type Variant/String

Edit:

Split(Categories, ">")(0) return entire Tools & Home Improvement › Power & Hand Tools › Power Tool Parts & Accessories › Woodworking Project Plans & Kits › Woodworking Project Kits

It doesn't split the Categories variable.

Upvotes: 0

Views: 1006

Answers (1)

FunThomas
FunThomas

Reputation: 29296

Split returns an Array of Strings. However, Debug.Print cannot print an Array. You need to specify an index:

Debug.Print Split(Categories, ">")(0)

or

Dim tokens() As String, i As Integer
tokens = Split(Categories, ">")
For i = LBound(tokens) To UBound(tokens)
   Debug.Print i & ". substring: " & Trim(tokens(i))
Next i

Upvotes: 1

Related Questions