98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Splitting a Cell into Multiple Cells VBA
Splitting a Cell into Multiple Cells VBA
Resolved · Medium Priority · Version 2007
Tue 24 Dec 2013, 15:53Delegate Imran said...
Imran has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Excel Advanced - Formulas & Functions course
Excel Advanced - For Power Users course
Splitting a Cell into Multiple Cells VBA
Hi,
I have the code below, and from what I understand is that it says something along the lines of split the cell a1 every time there is a space. Then count the number of separate elements in the cell A1 an then transpose the values.
What I don't understand is how the values in A1 are actually pasted into the cells below. Where does it say that the cells should be first delivered from the memory to the actual cells. Second thing is, if I want not to transpose but rather paste them into cells, lets say A2 going into b2, b3, b4 etc until all the cells required are used up and then stop.
The cell A1 looks like this Mon Tues Weds Thurs Fri Sat Mon2 Tues2
Option Explicit
Sub SplitCellA1()
Dim strValue As String
Dim arrParts As Variant
strValue = Range("A1").Value
arrParts = Split(strValue)
Range("A1").Resize(RowSize:=UBound(arrParts) + 1).Value = _
Application.Transpose(arrParts)
End Sub
Tue 24 Dec 2013, 16:51Trainer Gary said...
RE: Splitting a Cell into Multiple Cells VBA
Hi Imran
Thanks for getting in touch.
There's a couple of shortcuts in this code which is obscuring how it actually works. There are two functions in particular: SPLIT and TRANSPOSE.
If you don't specify which delimiter (splitting character) to use, SPLIT assumes you want to use a space character. So that takes care of the separation.
TRANSPOSE converts a horizontal array to a vertical one or vice-versa.
This line
Range("A1").Resize(RowSize:=UBound(arrParts) + 1).Value = Application.Transpose(arrParts)
says: "make a range starting from A1 as many rows as there are parts in the array. Then TRANSPOSE outputs the array items."
If arrays are a bit baffling we'll be covering arrays on the Advanced course in January. They're a really useful programming concept to be aware of.
It's actually a very efficient and well-written piece of code. Look up both of the functions in VBA help for more info.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 24 Dec 2013, 17:21Delegate Imran said...
RE: Splitting a Cell into Multiple Cells VBA
Hi Gary,
I didn't expect a quick response on Christmas Eve, but thanks for that anyway!
I completely don't understand the arrays so I'm glad we will cover that at the next level. I got the code from the MSDN forum for developer.
I will have a look at what you suggested!
Have a Happy Christmas!
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:LARGE and SMALL functions and their usesTwo of Excel's most common functions are the MAX and MIN functions which will display the largest (MAX) or smallest (MIN) value in a series. What if you need the 2nd or 3rd largest or smallest values instead of the largest or smallest? |
