Using find function in a vba macro

[marcospaterson]

Hey all.
Once again, a weird query.
How can I use the "FIND" function in a vba procedure?
What i want is:
I have a variable that comes up with email address I have in a sheet.
IŽd like to use these email addresses to retrieve the string after the "@" character.
I came up with the formula mid(variable, find(variable, "@"), 100) and it works fine in the spreadsheet but wont work if writen in the code.
Does anyone knows how to code this function properly?
thanks.

[tigeravatar]

marcospaterson,

You would use the application.worksheet object, with a little bit of code, like this: (assumes the email address is in cell A1)

Sub getdomain()
    Dim domain As String
    domain = Right(Range("A1").Value, Len(Range("A1").Value) - Application.WorksheetFunction.Find("@", Range("A1")))
    MsgBox domain
End Sub

Hope that helps,
tigeravatar

[marcospaterson]

YEP! works fine!
Thanx a lot tiger!