splitting text/finding data

[kroz]

Hey guys,
I'm kinda new to VBA but i need to implement a search in a text box. I have a form containing a text box in which the user inputs data in the following way:

"owner of the account * NAME (other data)"
"owner of the account * NAME (other data)"
..
(multiple lines - up to 15)
I created a text box to extract the Name from the text with the following code:

Private Sub ButChange_Click()

Dim Text As String
Dim Line As String
Dim Ntemp As String
Dim Name As String

'Init
Ntemp = ""
Name = ""
Line = ""

'temporary text to be parsed
Text = Chr(13) & TboxII.Text & Chr(13)
'while not temp text is empty
While InStr(Text, Chr(13)) <> 0
  'take first line of text
  Line = Left(Text, InStr(Text, Chr(13)) - 1)
  'selecting the name
  If InStr(Line, "You still have to kill") Then
    Ntemp = Trim(Right(Line, Len(Line) - InStr(Line, "*")))
    Name = Trim(Left(Ntemp, Len(Ntemp) - InStr(Ntemp, "(")))
    ActiveCell.Value = Name
    Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
  End If
Wend

When i run the form my excel crashes. Any idea on what i did wrong?

P.S. A similar post is here - i modified the code a bit: http://www.excelforum.com/excel-programming/674258-combo-list.html#post2052687

 

[kroz]

I found the error, but now the code doesn't work as it should:

Private Sub ButChange_Click()

Dim Text As String
Dim Line As String
Dim Ntemp As String
Dim Name As String

'Init
Ntemp = ""
Name = ""
Line = ""
TboxIO.Text = ""

'temporary text to be parsed
Text = Chr(13) & TboxII.Text & Chr(13)
'while not temp text is empty
While InStr(Text, Chr(13)) <> 0
  'take first line
  Line = Left(Text, InStr(Text, Chr(13)) - 1)
  'Name extraction
  If InStr(Line, "Owner of the account ") Then
    Ntemp = Trim(Right(Line, Len(Line) - InStr(Line, "*")))
    ActiveCell.Value = Trim(Left(Ntemp, Len(Ntemp) - InStr(Ntemp, "(")))
    Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
  End If
    'remove the line
    Text = Right(Text, Len(Text) - InStr(Text, Chr(13)))
Wend

I forgot to put the condition to get out of the loop. The problem now is that it doesn't take all the data in the name.
if i input "Owner of the account * John McDee Erby (max access account)"
it will take "John McDee Erby (ma".
In other recordings it didn't take the whole name..

Any idea why?

 

[kroz]

yeah, i found the solution..
here's a link: http://www.excelforum.com/excel-programming/674258-combo-list.html#post2053273

 

[westconn1]

try like
= Trim(Left(Ntemp, -1 + InStr(Ntemp, "(")))