2 words in A Cell split into 2 textBoxes on userform

[rlink_23]

I always start my new thread as "I have this sheet" or "I have this userform. I am not going to do that today.. lol I am going to start by thanking all of you and your contributions to helping me learn and understand VBA. I took this project on that it might be over my head a little bit and actually have only gotten stuck a few times. and There has always been someone to help me when needed. I appreciate all of you so very much. And I really mean that. Even if I have a post that gets no comments it drives me to figure it out. Ok, now on to my Workbook.

This may be simple or it may not be doable, but on My UserForm I have First Name & Last Name, When the save button is clicked it saves the info in my sheet in one cell. But now I want the people using the workbook to be able to pull info back into the UserForm (I have this set up already except for these last few textBoxes) I can't figure out how to split the cell words back into their respected TextBoxes. Is this doable? am I able to Split the contents of a cell into 2 different TextBoxes? Thank you for any input you may have!!

 

[jproffer]

Dim Words() ...gotta have the parenthesis

 

[rlink_23]

Oh ya lol... silly me

 

[rlink_23]

So it works when I set focus to Just a single cell, but not ("I:I") as I need it to pick up which ever row the Quote number is in

 

[jproffer]

If you have it as you posted above, "I" hasn't been defined yet. I is defined when your loop starts. Try moving it below "For I = 2 To Lastrow"

 

[rlink_23]

Oh snap. You are right

 

[jproffer]

BTW, Cells(I,I)will move diagonally through the worksheet. IOW, row 2/col 2....row 3/col 3.....row 4/col 4...etc. Is is always incrementing. Maybe that's what you wanted...I just happened to notice

 

[rlink_23]

That gives me a type mismatch. The For I = 2 to last row was for the original coding that transfers data from sheet to text boxes. I think that being I and the data I want to split being in Column I is giving me the type mismatch. Can I change the For I = 2 can I change that to an x Maybe?

 

[rlink_23]

So in column A is the quote number. When someone puts the quote number in textbox9 it pulls all that data back into the userform. But I need it to split the first and last name (same cell) which are actually in column I. So when the user inputs the info it splits into textbox1 and textbox2

 

[rlink_23]

Private Sub TextBox9_Change()Dim I As Long, LastRow As Long
LastRow = Sheets("Quotes").Range("A" & Rows.Count).End(xlUp).Row
For I = 2 To LastRow
If Sheets("Quotes").Cells(I, "A").Value = TextBox9 Or _
Sheets("Quotes").Cells(I, "A").Value = Val(TextBox9) Then
Dim Words() As String
Words = Split(Range(I, "I").Value)
Me.quotenumber = Sheets("Quotes").Cells(I, "A").Value
Me.Date1 = Sheets("Quotes").Cells(I, "B").Value
FirstName.Value = Words(0)
Me.size = Sheets("Quotes").Cells(I, "D").Value

 

[rlink_23]

still working on this.. Pretty much all day I have tried everything I can think of.. Don't know how to define the I Row.