vba vlookup variable

[bensonsearch]

Hi All,

I have a vlookup that looks at customers and sees if the email address is in the data, this works fine until i start adding them with ; at the end.

I tried changing the variable (call it asd) that the vlookup sets to, to have asd = asd + ";" also tried asd = asd & ";" none work

i have the asd set to variant since string makes an error, is there a way to make this work or a different data type i can use?

let me know if i make no sense

 

[Norie]

It might make more sense if we saw the code.

 

[bensonsearch]

Fair enough

vlook = TextBox1.Text 'textbox1.text is the customer name
 
value = Application.VLookup(vlook, Worksheets("Sheet1").Range("A2:L" & count), 7, False)
'row 7 is the email address with ; at the end for mass emailing
If IsError(value) Then'code to add customer (works fine)
else
'code if a match is found
end if

the error is a match will never be found as the emails are stored with ; at end but the textbox with the new details will not. hope this helps more, can paste full code if really needed

 

[Norie]

So you've tried vlook & ";"?

 

[bensonsearch]

vlook is the name but i have tried at the end of the vlookup and for value (value is the email vlook customer name)

 

[bensonsearch]

ok i have done a work around for it so this works yay, thanx for trying to help

 

[Norie]

Can you explain what you mean by adding ';' at the end if them?

If you mean the emails in Column A then you should just be able to add ';' at the end of the the variable vlook.

 

[bensonsearch]

Can you explain what you mean by adding ';' at the end if them?

If you mean the emails in Column A then you should just be able to add ';' at the end of the the variable vlook.

the emails are stored with ;
eg email@email.com;

i tried adding it to the end but it didnt work at first as i was vlookuping the name and returning the email

I have now changed it to be looking up email and it works (email was in coloum 7 now in coloum1)

 

[Norie]

Sorry you've lost me.

The name column was unchanged but the lookup stopped working?

That shouldn't really happen.

By the way, have you considered using a listbox or combobox instead if a textbox?

Then you wouldn't need the lookup.