I used End(xlup) to get the last row. Then change your DO loop to a for
Loop. See changes below.


Sub GETFIGS()
With Workbooks("TEST.xls").Sheets(1)
LastRow = .Range("A" & Rows.count).end(xlup).row
for RowCount = 1 to LastRow
if .Range("A" & RowCount) < "" then
Data = .Range("A" & RowCount)
AMT = .Range("F" & RowCount)

With Workbooks("DATA.xls").Sheets(1)
Set R1 = .Rows(2).Find(What:=Data, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not R1 Is Nothing Then
Set C1 = .Columns("A:A").Find(What:=nm, _
LookIn:=xlValues,LookAt:=xlWhole)

If Not C1 Is Nothing Then
..Cells(C1.Row, R1.Column) = AMT
End If
End If
End With
end if
Next RowCount
Loop
End With


"K" wrote:

Hi all, In below macro i am putting data in appropriate cells from
Workbooks("TEST.xls") to Workbooks("DATA.xls"). In Workbooks
("TEST.xls") i have "Data" from range A1 to A20 in which A5 and A10
cells are blank. The below macro looks up "Data" only from Range A1 to
A4 and as there is nothing in cell A5 and because of i got line in my
macro saying " Do While .Range("A" & RowCount) < "" " it dont looks
up data after cell A5. It supposed to go from Range A1:A20 igonring
any blank cells in between. Please can any friend tell me how can i
improve my macro.

Sub GETFIGS()
With Workbooks("TEST.xls").Sheets(1)
RowCount = 1
Do While .Range("A" & RowCount) < ""
Data = .Range("A" & RowCount)
AMT = .Range("F" & RowCount)
With Workbooks("DATA.xls").Sheets(1)
Set R1 = .Rows(2).Find(What:=Data, LookIn:=xlValues, LookAt:=xlWhole)
If Not R1 Is Nothing Then
Set C1 = .Columns("A:A").Find(What:=nm, LookIn:=xlValues,
LookAt:=xlWhole)
If Not C1 Is Nothing Then
..Cells(C1.Row, R1.Column) = AMT
End If
End If
End With
RowCount = RowCount + 1
Loop
End With

End Sub