No offence dude but worst phrased question ever! Also, formatting!
TL;DR, here's what I think you need
Put this in C4, it will translate the A to D into the sheet name.
Now in D4 we will calculate the index or row number in your chosen sheet ...
To prevent the code from stopping after the first blank cell is found, you need to remove Exit Sub. You need to have a counter that will be incremented after a blank cell is found and the message written on a cell on the report sheet (in my code below, the report sheet is Sheet3).
Your code will look like the below. i is the counter. I have removed the ...
Is there a space between the 8 and the hyphen? Always, sometimes, never?
That would throw off your formula.
More generally, I think you have overcomplicated this by using LEN. Your FIND already shows the position of the hyphen from the start of the string, which indicates the length of the text/number before it.
So something like
Adapt the code below. It gets the last non-blank cells in row 1 (assuming the contents of the row are headers). This allows to get the last column. Then loop through each column and row (if necessary).
Dim lRow, lCol As Long
'Find the last non-blank cell in row 1
'This assumes that the first row has column headers
The built in Word "find and replace" tool can do 90% of the work for you. It can search on page breaks, paragraph marks, spaces and so on if you use the "More >>" option where a "Special" drop down button lives.
That "Special" button will allow you to select paragraphs, various different whitespace characters, ...