Counting the number of occurances of a word in a column of variable length

Hello Y'all,
I have a spreadsheet with adverse event data. Column A has the adverse event term exactly as the staff member entered it into the database. I am working on creating a report that counts the number of instances that an AE term appears in column A. The issues that I am having trouble with are that there are MANY AE terms, the AE terms are variable (and unpredictable), and the number of rows in the spreadsheet is variable.
I have figured that the easiest, most organized thing to do is to create a new sheet as the destination for the number of occurances. Below is my code with the trouble area in red:

Please Login or Register  to view this content.

I got some numbers and the macro ran with no error messages. However, I can't tell if the numbers ouput by this macro are correctly counting the number of times a word appears in column A. I have attached a mock up of what the final report needs to look like .

Any and all help is very much appreciated! Thanks, y'all!

I have attached a sample copy of the source data sheet

The sponsor of the study sent those specs. The logic is kind of backwards from that point. The report they want can't be changed. But I have, in my macro for the results to be put in a new worksheet. So I download the raw data from the database, and then run my macro (that's unfinished). My logic is for every cell in column A, LRow, count the number of times the value of Column A, LRow appears in Column A then post that count in the destination cell in worksheet 2. So for example, say the word headache appears in worksheet 1, Column A a total of 7 times, then the destination cell in worksheet 2 (the first blank cell in column E) will show the value of 7.

Thank you for your suggestion, but I get the sense you haven't looked at all of the macro code and that you have missed some key parts of my issue.
The sample data I posted is just that, sample data. The raw data has about 1500 rows and will grow/change as the study is ongoing. Also, I account for empty ceells in my macro by deleting them. Last, this report has to be accessible more than one time and by more than just me.