Moving data from worksheet to columns

Hi,

I have a new/different requirement.

Could you please assist with updating the code so that the hyperlinks are captured from worksheet "Company names" and placed into column D within "Sheet1".

The macro should check for any words after the "at" or "@" word/sign (within column E), and if there are any exact words found after the "at" or "@" then the macro should take the URL from within worksheet "Company names" and put them into column D. If no words are found after the "at" or "@" sign then the cell should be marked as "-".

I have attached the spreadsheet with BEFORE and AFTER.

I look forward to your assistance.

Thank you,
Ash