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.
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
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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.