Ending a Do Loop when Cell used in Vlookup is Blank

Question

My goal is to take the data in column M of Excel #1 to Vlookup the name in the Excel #2 and fill in Excel #1's column C.

I can't get my loop to stop when I reach a blank cell in column M. Data starts at line 7.

Range("C7").Select

Do While Wroksheets("Sheet1").Range("M" & Rows.Count).End(xlDown).Row <> ""
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[10],'excel.xlsx'!Table46[[column1]:[column7]],7,FALSE)"
    ActiveCell.Offset(1, 0).Range("A1").Select
Loop

Answer 1

No need for a while loop here. The relative R1C1 formula allows you to write all formulas at once:

Dim ws As Worksheet
Set ws = Sheets("Sheet1")
  
Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "M").End(xlUp).Row

With ws.Range("M1:M" & LastRow)
  .FormulaR1C1 = "=VLOOKUP(RC[10],'excel.xlsx'!Table46[[column1]:[column7]],7,FALSE)"
End With

Answer 2

Write Formulas to a Column Dynamically

Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")

Dim rg As Range

With ws.Range("C7")
    Dim lCell As Range: Set lCell = .EntireRow _
        .Resize(ws.Rows.Count - .Row + 1) _
        .Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If lCell Is Nothing Then Exit Sub ' no data found
    Set rg = .Resize(lCell.Row - .Row + 1)
End With

rg.FormulaR1C1 _
    = "=VLOOKUP(RC[10],'excel.xlsx'!Table46[[column1]:[column7]],7,FALSE)"