Don't know whether this helps, but a much quicker way of splitting up
your addresses in Excel is to select the column and use the "text to
column" feature under the "data" menu. Just set the delimiter character
to other and put in the @. (Make sure the column to the left of the one
containing your addresses is blank.) The first part of the address  will
be in the first column, and everything after the @ will appear in the
second column.

i dont know what is going on with your code, but i think using function like len
and mid may be easier. for example, you can loop this, but the logic would be

characternumber = len(cells.value) --this counts the number of characters in
your domain
for (row 1 to end)
characternumber = 0
counter = 0
for i = 1 to characternumber
if left(cell.value,i)=@
counter = counter  +1 (counter should be set to 0 at the beginning of
each row  loop)
exit for
else: counter = counter +1
end if
next
cells(1,2).value = mid(cell.value,counter,characternumber - counter) (i don't
know if the beginning of this code is correct, but what i'm trying to do is put
the domain  name in the cell to the right of the current one)
next

the mid may be off by one character  on either the first or second integer
numbers, which you can fix by adding a +1. you'll just need to experiment.

I know you have had other replies, but ...

Notice that all the Excel functions are given one or more capital letters by the
VBA editor.

Notice that substring hasn't got any. I.e. substring is not recognised. Get in
the habit of noticing this, as it's a dead giveaway.