Search code examples
excelvbasplituserform

use SPLIT function to seperate a list of numbers from a userform

I have a userform that has a list of numbers entered in column style with no separator other than being on a separate line. Entry into the user form will look something like this:

2172223333
2172223334
2172223335
2172223336
2172223337

How do I use the SPLIT function to separate this bulk entry into the user form into separate cells in the same column on a spreadsheet? I'm familiar with how it works if there is a comma or space to look for between the terms but will it somehow recognize a separate line as a delineator and if so, what symbol do I use to represent that in the code?

I want to be able to read this input and have it entered in a workbook, say ("workbookA") and sheets(1) and put it in Column A starting at row 2 and going downward staying in column A.

Solution

  • Try this:

    myString = ...
data = split(myString, vbcrlf)
workbooks("workbookA").worksheets(1).range("A2").resize(ubound(data)+1,1).value = application.transpose(data)

    myString is the String with your data.

    In VBA you have the constants vbLf, vbCr, vbCrLf for linefeed, carriage return and linefeed + carriage return (https://en.wikipedia.org/wiki/Newline), which represent the characters.