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

What is the criteria for knowing what the last row is? You said that it
should process from A1 to A20. However, is A20 always the last cell to
process or can it vary and if so will it be the last cell in column A that
has data?

--
Regards,

OssieMac


"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

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

Hi OssieMac, Thanks for replying. Yes A20 will always be the last
cell and it will be alwasy column A

Joel's code should work fine provided that row 20 is the last row of data in
column A. If not and you just want to process the 20 rows then change the
following line of Joel's code.

Replace this
LastRow = .Range("A" & Rows.count).end(xlup).row
with this
LastRow = 20

--
Regards,

OssieMac


"K" wrote:

Hi OssieMac, Thanks for replying. Yes A20 will always be the last
cell and it will be alwasy column A

thanks lot Joel. you are the man