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
>
|
Similar Threads
