PDA

View Full Version : [SOLVED] Split function in vba



idnoidno
08-12-2017, 07:11 AM
20067

I have not understood how to use the Split function, although I have looked at some of the information using the dictionary method, hope someone can tell me how to use it in my file.

snb
08-12-2017, 07:19 AM
split only splits 'strings'.
There's no relation between Dictionary and split.

mdmackillop
08-12-2017, 07:34 AM
This produces a nonsense result but at least it does something.
You need to ADD keys before you can do anything with them. You cannot SPLIT all keys in one action

Option Explicit

Sub t1()
Dim d As Object, i%, j%, ar, br, k


Set d = CreateObject("scripting.dictionary")
ar = [a2].CurrentRegion
For i = 1 To UBound(ar)
If Not d.exists(ar(i, 1)) Then
d.Add ar(i, 1), ar(i, 2)
End If
Next i


For Each k In d.keys
br = Split(k, "-")
For i = 1 To d.Count
For j = 0 To UBound(br)
Cells(i + 1, j + 5) = br(j)
Next j
Next i
Next k
[d2].Resize(d.Count) = Application.Transpose(d.items)
Set d = Nothing


End Sub

SamT
08-12-2017, 07:48 AM
Split returns an array
Split has 2 parameters, the String, and the Separator, both enclosed in Double Quotes and with a comma between:

Split("String", "Separator")
Split("Long,String,separated,By,A,Separator", ","), in this case commas.

But can be Comma+Space (", "), or just a space (" ")
Can even be a Word ("LongWordStringWordSeparateWordByWordAWordOtherStringWordPeriod") or a Carriage Return (vbLF) or any ascii String (Chr(255)&Chr(125)&Chr(222))

Sub Test_Split()
Dim X As Variant
X = Split("Long,String,separated,By,A,Comma", ",")

MsgBox X(LBound(X))
MsgBox X(2)
MsgBox X(UBound(X))
End Sub

idnoidno
08-12-2017, 07:59 AM
I did not expect someone to answer my question so soon.
Mr.mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop)
Thank you for telling me that
"You cannot SPLIT all keys in one action"
I do understand.
Thanks for all.

snb
08-12-2017, 09:40 AM
Sub M_snb()
sn = Sheets(1).Cells(2, 1).CurrentRegion

With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)
sp = Array(sn(j, 2), Split(sn(j, 1), "-")(0), Split(sn(j, 1), "-")(1))
If .exists(sn(j, 1)) Then sp(0) = .Item(sn(j, 1))(0) + sp(0)
.Item(sn(j, 1)) = sp
Next

Sheets(1).Cells(2, 4).Resize(.Count, 3) = Application.Index(.items, 0, 0)
End With
End Sub

BTW. You can split all keys in 1 action:


Sub M_snb()
sn = Sheets(1).Cells(2, 1).CurrentRegion

With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)
sp = Array(sn(j, 2), Split(sn(j, 1), "-")(0), Split(sn(j, 1), "-")(1))
.Item(sn(j, 1)) = sp
Next

st=split(join(.keys,"-"),"-")
Sheets(1).Cells(2, 4).Resize(ubound(st)+1) = Application.transpose(st)
End With
End Sub

More on Dictionaries: http://www.snb-vba.eu/VBA_Dictionary_en.html

mdmackillop
08-12-2017, 10:31 AM
BTW. You can split all keys in 1 action
Very clever.