Help changing macro to function, selecting based on cell being text

So I'm pretty new to using excel VBA, I've mainly worked with Access and it's different.I recorded a macro and then altered it so that I could use it over an over again relative to a selected cell. The issue is that I don't have to have to run this macro over 1000 times and was looking for a way to create a loop it somehow.
My issue is I don't know how to specify where to start/end the loop.

The data that I have looks like this:
example1.PNG

The macro that I made is the following:

Sub Overall_Percentages()
'
' Overall_Percentages Macro
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
    
    r = ActiveCell.Row
    c = ActiveCell.Column
    
    Let start1 = "F" & r + 1
    Let start2 = "G" & r + 1
    Let start3 = "H" & r + 1
    
    Range(start1).Select
    ActiveCell.FormulaR1C1 = "=RC[-4]/R" & r & "C2"
    Range(start2).Select
    ActiveCell.FormulaR1C1 = "=RC[-4]/R" & r & "C3"
    Range(start3).Select
    ActiveCell.FormulaR1C1 = "=RC[-4]/R" & r & "C4"
    Range(start1 & ":" & start3).Select
    ' Find where the last code is for H value in autofill
    
    Selection.AutoFill Destination:=Range(start1 & ":" & "H" & r + 20), Type:=xlFillDefault
End Sub

Main issue is that the number of codes (in this case 1 through 5) varies from 18 to 20 , so its not always the same. Manually I just went for the largest number and then worked over it, but in a function that wouldn't work?

I was thinking along the lines of: If the cell in column A is text then set as active cell, do macro, then find next text cell.

Ideas? Thanks!