Trouble with Vlookup in VBA

[TurboDieselOne]

Hi

Have this code when you double click on a cell a little form opens with the contents in a multiline text box. Fine works now i need this text box to look up somthing on the activecells contents using vlookup

example cell contents


ARKWRIGHT ROAD--NW3--60643726--4530856866--213185 03/05/2011

Private Sub Userform_Activate()
TextBox1.Value = ActiveCell & Chr(10) & (Application.WorksheetFunction.VLookup(Left(Right(TextBox1.Value, 21), 6), Notes, 16, False))
End Sub

But would also like to add an if statement that this only applies on the condition the Textbox does not have any CHR10's (1 Line Only)

Thanks

PS this will go a long way in cleaning up a great app

 

[unigee]

You could use Instr

    s = "This is line 1" & Chr(10) & "and this is line 2"
    MsgBox InStr(1, s, Chr(10))

this will return the position CHR(10) is located in the string, if the return value is 0 then there are no CHR(10)

'''''' Not tested
Private Sub Userform_Activate()
if Not Instr(1, TextBox1.Value, chr(10)) = 0 Then
TextBox1.Value = ActiveCell & Chr(10) & (Application.WorksheetFunction.VLookup(Left(Right(TextBox1.Value, 21), 6), Notes, 16, False))
End If
End Sub

 

[TurboDieselOne]

I cant get the vlookup bit working though Get that first and set that in a if then situation ie if no chr(10)'s then Use the vlookup part if not then use the active cell contents the fill TextBox1. The vllokup dont work though The Range for the Vlookup is Sheet2! C2:AB1000 and column 15 for example

 

[TurboDieselOne]

This has to work like this

TextBox1 on the Userform = active cell
Then If TextBox1 contains more than 1 line then end else
TextBox1 = the value of the active cell & the value of the Text in a vlookup as below

=Vlookup(LEFT(RIGHT(the Activecell,21),6),15,False)

if (Vlookup(LEFT(RIGHT(the Activecell,21),6),15,False)) has got contents in the cell other wise if not then add the word "No Notes Found" to end of TextBox1

Can you grasp what i'm upto


Many Many thanks