why doesnt this work

[mckirk]

hi,
im new to programming and cant figure out why this doesnt work. I have two excel spreadsheets with data on them. One is a small list of people (about 800) who are on the report for a specific type of activity. The other is a very large list of people (about 22000) who are on a report for a broad type of activity. Everyone on the small list is on the large list. Also, every one who is on the short list appears only once on that list but they appear several times on the large list. What I am trying to do is to copy every time they are on the large list to the small list but I cant seem to figure out how. I wrote this code but it seems to do absolutely nothing. Could someone tell me where i went wrong? Heres my code:


Sub copypaste1()
Dim fcells As range
Dim testrange As range
Dim adressfind
Dim q As range
Dim cs As range
Dim v As Long
Dim n As Long
Dim ranger As range
Dim cm As range
Dim addressfind

n = 0

Set ranger = Workbooks("wirespike.xls").Sheets("sheet1").range("b1:b2000")
For Each cm In ranger
If cm.Value <> "" Then
n = n + 1
Else
End If
Next


Set testrange = Workbooks("wirespike.xls").Sheets("Sheet1").range("b2:B" & n + 1)
Set q = Workbooks("wirespike.xls").Sheets("Sheet1").range("B" & n + 1)

For Each cs In testrange
Set fcells = Workbooks("wires.xls").Sheets("117").range("b2:b28000").Find(what:=cs.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not fcells Is Nothing Then
addressfind = fcells.Address
cs.Offset(0, 3) = fcells.Offset(0, 7)
cs.Offset(0, 4) = fcells.Offset(0, 8)
cs.Offset(0, 5) = fcells.Offset(0, 9)
fcells.FindNext

Do While fcells.Address <> addressfind And Not fcells Is Nothing

q.Value = fcells.Value
q.Offset(0, 2) = fcells.Offset(0, 1)
q.Offset(0, 3) = fcells.Offset(0, 7)
q.Offset(0, 4) = fcells.Offset(0, 8)
q.Offset(0, 5) = fcells.Offset(0, 9)
n = n + 1
fcells.FindNext
Loop

Else

End If
Next


End Sub

 

[Zathras]

You might be better off not using VBA.

Instead, use VLOOKUP in a working column of a copy of the larger sheet to indicate whether that line has a corresponding entry in the smaller sheet or not. Then copy and paste special values and sort on the column to bring the ones you want together.

Finally, you can delete the unnecessary rows and columns and then copy the data from the larger sheet to the smaller.

(If you want to automate it with VBA, you can record the whole procedure with the Macro Recorder.)

If you still want to work with the code you have, consider that incrementing n (near the bottom of the Do While) is accomplishing nothing. You never use the value again after entering the "For Each cs in testrange".

Also, look up in the help file for an example of how to use .FindNext -- Hint: both the .Find and .FindNext methods need to be applied to the same range. You are using .Find with "Workbooks("wires.xls").Sheets("117").range("b2:b28000")" but you are using .FindNext with "fCells" -- those are not the same range.