excel vba - simple split with no loop

Question

I am learning Excel-VBA and I am trying to learn how to do things that will help me in the future. I have been trying to learn how to do a simple split without a loop. Although I got a code that I could use, I could not understand it.
Excel VBA- Copy and paste text on left after CHAR(10)

I have this mock from what I learned in the code I was pointed towards. I feel like I am close but no matter what I do I can not make the syntax work. Any help would be greatly appreciated.

I attached the code and a snippet of what I would like to see before and after. My code does not work in this line.

Worksheets("Sheet1").Cells(1, 2).Value = fullname

The code is below

   Sub test()

    Dim txt As String
    Dim fullname As String

    txt = Worksheets("Sheet1").Cells(1, 1).Value
    fullname = Split(txt, Chr(10))

    Worksheets("Sheet1").Cells(1, 2).Value = fullname

    End Sub

enter image description here

Gary's Student · Accepted Answer · 2015-05-21 15:11:54Z

1

It fails because fullname is an Array. To get the first element:

Worksheets("Sheet1").Cells(1, 2).Value = fullname(0)

answered May 21, 2015 at 15:11

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darren Bartrup-Cook · Accepted Answer · 2015-05-21 15:23:49Z

As Gary's Student says - fullname will be an array. The first problem you'll have is a type mismatch as you've declared fullname as a string.

You can paste an array into a range of cells or reference elements of the array as Gary said in his answer.

Sub test()

    Dim txt As String
    Dim fullname As Variant

    txt = Worksheets("Sheet1").Cells(1, 1).Value

    'This will be an array containing 4 different values
    'based on your A1 cell containing 3 line breaks.
    fullname = Split(txt, Chr(10))

    With Worksheets("Sheet1")

        'The array must be pasted into 4 cells.
        '.Range(.Cells(1, 2), .Cells(1, UBound(fullname) + 2)) = fullname

        'the above line is the same as:
        .Range("B1:E1") = fullname

    End With

End Sub

Collectives™ on Stack Overflow

excel vba - simple split with no loop

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related