CODEWITHSUNDEEP
excelvba
K.W.
K.W.

Reputation: 141

VBA - Split function and convert it into integer value

Cell D1 is "12 x 15KG" sometimes it might be "5 x 15KB"

this_packing = Val(Split(Worksheets("DA").Range("D1").Value, "X"))

The above return "Type Mismatch error 13" How can I get the Integer value into 'this_packing' ?

Thanks!

Upvotes: 0

Views: 1588

Answers (2)

Tim Hall
Tim Hall

Reputation: 1505

Split returns an array (even if the delimiter isn't found, it'll return an array containing the original value as the first element) so you'll need to specify the index of the part to be converted:

' First element: 12 in "12 x 15KG"
first = Val(Split(Worksheets("DA").Range("D1").Value, "x")(0)) ' -> 12

' Second element: 15 in "12 x "15KG"
second = Val(Split(Worksheets("DA").Range("D1").Value, "x")(1)) ' -> 15

It can be kinda confusing to determine whether to use a 0-base or 1-base indexing, but Split uses 0-base by default.

Upvotes: 1

dcromley
dcromley

Reputation: 1410

1) Split returns an array, so Val(Split(..)) is invalid.
2) The Split is case sensitive, so you need "x" to match "X".

Option Explicit

Sub sub1()
  Dim i1&, s0$, s1$, this_packing As Variant
  this_packing = (Split("12 x 15KG", "x"))
  s0 = this_packing(0)
  s1 = this_packing(1)
  i1 = Val(this_packing(0))
End Sub

Upvotes: 1

Related Questions