Posts by aswee

Hello!

I am trying to put together an idiotproof way of inputting data and VBA is not my real forte.

I have a block of text that is copied into a textbox and then when the button is pressed I want the text to start appearging in cell A1 and then be split appropriately. I am using the following code which splits the rows based on a linebreak in the textbox entry :

Private Sub CommandButton2_Click()
Dim Str As String, a
Dim cnt As Integer
Dim w()




Str = TextBox1.Value
a = Chr(10)
cnt = UBound(Split(Str, a))




ReDim w(1 To cnt + 1, 1 To 1)
For i = 0 To cnt
    w(i + 1, 1) = Split(Str, Chr(10))(i)




Next i
ActiveSheet.Range("A1").Resize(i, 1) = w




ActiveSheet.Cells.Replace Chr(13), " "
ActiveSheet.Cells.Replace "Edit", " "




End Sub

However I want to modify it so that it does slightly more, namely :

1. Where there is a tab in the input text, it splits the data into the next column
2. Where there is a line break in the input text, it splits the data into the next row
3. Validates so that if the first word is not "Bob" then it throws an error

Sample data would be eg :

Bob Kate Fred
Jane Eric Terry
Simon Andrew Flora

So that it would appear as :

[TABLE="class: grid, width: 200"]

Bob

Kate

Fred

Jane

Eric

Terry

Simon

Andrew

Flora

[/TABLE]

where the number of input lines is variable . Hope that this makes sense!