Continuous running in a loop

If you find that the macro runs in a loop through the CAS numbers and does not seem to stop then it is likely you have a duplicate CAS number.

Check the list of CAS numbers for a duplicate either by eye or using Excel by going to Data tab and selecting 'Remove Duplicates'.

(Don't forget to ensure that if you remove a duplicate you have checked that the list is now accurate and matches your true formula)

Problem CAS numbers

2305-05-7

4940-11-8

With these CAS numbers, Excel re-formats the CAS into the date format which means they appear red (not present even when they are in the database). So for these CAS you need to do the following: 

1) Select the box with the problem CAS and delete CAS

2) Right click > Format cells...

3) Under the Number tab choose 'text' > click ok

4) Type in the CAS again. 

Repeat for all problem CAS (when you know the CAS is in the database but it appears red).