[SOLVED] Loop Excel code for each line until blank cell is found

Loop Excel code for each line until blank cell is found

I have the following code That I need to loop for ~100 rows. Instead of writing out for each row, is there a way to add a loop feature in here to repeat until a row is blank? I am having trouble figuring out the Do While Loop feature and incorporating it within the code below. Thanks!

 Sub Excel_INDIRECT_Function()

 'declare a variable
    Dim ws As Worksheet

 Set ws = Worksheets("TOC")

    'apply the Excel INDIRECT function

ws.Range("$F8").Formula = "=INDIRECT($W8&""Q24"")"
ws.Range("$G8").Formula = "=INDIRECT($W8&""Q30"")"
ws.Range("$I8").Formula = "=INDIRECT($W8&""I56"")"
ws.Range("$J8").Formula = "=INDIRECT($W8&""Q34"")"
ws.Range("$K8").Formula = "=INDIRECT($W8&""D7"")"
ws.Range("$L8").Formula = "=INDIRECT($W8&""L56"")"
ws.Range("$M8").Formula = "=INDIRECT($W8&""M56"")"
ws.Range("$N8").Formula = "=INDIRECT($W8&""N56"")"
ws.Range("$O8").Formula = "=INDIRECT($W8&""O56"")"
ws.Range("$R8").Formula = "=INDIRECT($W8&""D6"")"

End Sub

I am looking to get this accomplished for multiple rows without writing this code ~100 times. Thanks so much.

Solution

I'm not sure you need a loop.

This code will put the formulas in columns F, G etc. from row 8 down to the last row of data in column W.

Sub Excel_INDIRECT_Function()
'declare a variable
Dim ws As Worksheet
Dim lngLastRow As Long

    Set ws = Worksheets("TOC")
    
    lngLastRow = ws.Range("W" & Rows.Count).End(xlUp).Row
    'apply the Excel INDIRECT function

    ws.Range("F8:F" & lngLastRow).Formula = "=INDIRECT($W8&""Q24"")"
    ws.Range("G8:G" & lngLastRow).Formula = "=INDIRECT($W8&""Q30"")"
    ws.Range("I8:I" & lngLastRow).Formula = "=INDIRECT($W8&""I56"")"
    ws.Range("J8:J" & lngLastRow).Formula = "=INDIRECT($W8&""Q34"")"
    ws.Range("K8:K" & lngLastRow).Formula = "=INDIRECT($W8&""D7"")"
    ws.Range("L8:L" & lngLastRow).Formula = "=INDIRECT($W8&""L56"")"
    ws.Range("M8:M" & lngLastRow).Formula = "=INDIRECT($W8&""M56"")"
    ws.Range("N8:N" & lngLastRow).Formula = "=INDIRECT($W8&""N56"")"
    ws.Range("O8:O" & lngLastRow).Formula = "=INDIRECT($W8&""O56"")"
    ws.Range("R8:R" & lngLastRow).Formula = "=INDIRECT($W8&""D6"")"

End Sub