[RESOLVED] E-mail address problem

[Resource Dragon]

I am trying to run a (rough) check on a column in an excel sheet to see if the user has entered valid e-mail addresses. In the organisation for which I work the format for an e-mail address is [email protected] Where there is more than one person with the same first and last names there is also a number after the firstname.lastname and before the @ symbol. As a general rule, if your e-mail address is [email protected] then your log-on to the restricted employee network is firstname.lastname (My log-on is one of the exceptions to this rule!) Therefore I think the most likely mistake users would make when filling out the spreadsheet is to leave off the “@myorganisation.gov.au”. To check for this, I want to use something like

For j = 1 to max_no
email_address = Cells(j + 1, 9)
If InStr(email_address, “@”) = 0 then
MsgBox(“This probably isn’t a valid e-mail address. Please check it.”
Cells(j + 1,9).Select
Cells(j + 1,9).Activate
GoTo Stop_Check
End if
Next j
Stop_Check:

The problem I have is that VBA doesn’t appear to recognise the @ symbol. How do I get around this?

[westconn1]

there is no problem with instr and @ in vba
works fine

there must be some other issue

edit: if you locale language can require @ to be escaped, try using the chr value
if instr(somestring, chr(64)) = 0 then

[Siddharth Rout]

What is the value of email_address at the time of error if you are getting any error?

[Resource Dragon]

The e-mail address is something like: [email protected] (with a few changes to protect people's privacy - but there are no other sorts of characters in the e-mail address). Basically, "Mr Abbott" is the first person on the list.

If you're wondering why I'm not searching for "myorganisation.gov.au" this is to allow for one or two cases where people may need to use an address other than their usual work address.

[Resource Dragon]

I haven't had a chance to have another look at my code today - I got caught up doing other things at work.

[Resource Dragon]

I've just tried the routine at home where it works fine, I think Pete must be right about there being some other problem with the code. (It's in a nested loop, so I've probably made a logic error with the work version - even the nested version works fine at home.) So this one is resolved. Thanks everyone, I appreciate your help.