[SOLVED] Ending a Do Loop when Cell used in Vlookup is Blank

Ending a Do Loop when Cell used in Vlookup is Blank

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

Solution

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