# Solved: Excel VBA Loop Until Last Cell in column



## mariaa33 (Aug 7, 2008)

I am trying to loop through a column and where there are blanks look up the value with vlookup. Initially I tried looping until cell is blank but of course that stopped the loop at the first blank. I changed it to loop until last row and column but I am sure I am not using the right syntax to do it. It loops great through row but continues past the last row. Any help is appreciated.


```
Dim UsedRng As Range, LastRow As Long
    Set UsedRng = ActiveSheet.UsedRange
    LastRow = UsedRng(UsedRng.Cells.Count).Row
    Range("H2").Select
    Do
        If IsEmpty(ActiveCell) Then
            ActiveCell.FormulaR1C1 = "=VLOOKUP(LEFT(RC[-7],8),Sheet1!C1:C3,2,FALSE)"
        End If
        ActiveCell.Offset(1, 0).Select
        Loop Until ActiveCell.Cells(LastRow, 8)
```


----------



## CodeLexicon (Oct 15, 2013)

```
Dim UsedRng As Range, LastRow As Long
    Set UsedRng = ActiveSheet.UsedRange
    LastRow = UsedRng(UsedRng.Cells.Count).Row
    Range("H2").Select
    Do Until ActiveCell.Row = LastRow + 1
        If IsEmpty(ActiveCell) Then
            ActiveCell.FormulaR1C1 = "=VLOOKUP(LEFT(RC[-7],8),Sheet1!C1:C3,2,FALSE)"
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub
```
You need the until element at the start of the loop and you want the loop to end having processed the last row.


----------



## mariaa33 (Aug 7, 2008)

Perfect. Thank you.


----------

