Identifying the last populated cell and stopping the loop when i

Hi

I have written the code below to do lots of look ups on some data. It all works fine but I can't seem to get the variable Data to work out the last populated cell. I know I need to use Range("A1").End(xlDown).Select but can't seem to get it right.

There are 2 sheets called Data and Look_Up and there is data in column A of each of them.

Please help
Thanks
Lyndsey

Sub LookUp()

Dim LookUp As String
Dim LookUpCount As Integer
Dim Cell As Range
Dim n As Integer
Dim Data As Range

LookUpCount = 2
LookUp = "A"

Do Until LookUp = ""

LookUp = Worksheets("Look_Up").Cells(LookUpCount, 1).Value
Set Data = Worksheets("Data").Range("A1:A7")


For Each Cell In Data

Cell.Activate
If Cell Like LookUp Then
If ActiveCell.Offset(0, 1).Value = Empty Then
ActiveCell.Offset(0, 1).Value _
= Worksheets("Look_Up").Cells(LookUpCount, 1).Offset(0, 1).Value
End If
End If

Next Cell

LookUpCount = LookUpCount + 1

Loop

End Sub