CODEWITHSUNDEEP
excelvba
clattenburg cake
clattenburg cake

Reputation: 1222

Splitting Strings And Printing Into Different Cells, Excel VBA

I'm new to VBA and have been cracking my head to solve this problem. I have a string of three characters which I want to split and place the values into an array in excel. So for instance, 

Sub Splitdata()

Dim x As Variant
Dim txt As String

txt = "Today is sunny"
x = Split(txt)

Range("C1:C3").Value = x

End Sub

However this only returns the first character from the string in cells C1-c3 i.e. "Today". I imagine a loop is required, however I'm not sure how to make one yet. Is there also a general way of solving this problem?

Upvotes: 0

Views: 4306

Answers (2)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19087

Yes, you could simply solve your problem by changing your splitting line into this one:

x = Split(txt, " ")

Edit: and change the line which passes result to sheet into this:

Range("C1:C3").Value = Application.Transpose(x)

Upvotes: 3

Tim Williams
Tim Williams

Reputation: 166885

When you assign a 1-dimensional array to a range, by default it will go horizontally (so C1:E1 would work OK), not vertically.

However, you can use Transpose() to flip the array so it will populate a vertical range: 

Range("C1:C3").Value=application.transpose(split("a b c"))

Upvotes: 2

Related Questions