VB question

[Guest]

This code picks up what is in cell B2 and copies it to every line. I need
something like a vlookup that will check every note number in the second
sheet and if it finds it in the first sheet copie the info for that
particular note number to the appropriate line. Can this be modified to work?
There can be numerous numbers - up to 5000+, which is why I need to do it in
vb. Any help would be appreciated.




Sub copydata()
Dim rng1 as Range, rng2 as Range
Dim cell as Range
with Worbooks("Book1.xls").Worksheets(1)
set rng1 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup))
End With
With Workbooks("Book2.xls").Worksheets(1)
set rng2 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup))
End With
for each cell in rng1
res = application.Match(cell.Value,rng2,0)
if not iserror(res) then
rng2.offset(0,36).Resize(1,16).copy _
Destination:=cell.offset(0,36)
end if
Next
End Sub

 

[Bob Phillips]

Wouldn't you juts use the same For Each loop, but invert the rng1 and rng2
variables, that is change rng1 for rng2 and rng2 to rng1 throughout.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

You mean as in change Book1 to Book2 and Book2 to Book1 - but assigning the
correct book was left to Marie when the code was provided.

 

[Guest]

I don't think the problem is in the book1 or book2. It is in the cell that it
refers to for the note number. As I said, it looks at the first number in
book1 that matches book2, which is B2 and copies the information for this
note to all 5000 accounts. Wouldn't this be because it is choosing this cell
instead of the entire column? Am I on the right track? I am pulling my hair
out trying to get this to work! I am trying to put a range of cells instead
of just the B2 cell but I'm still not having any luck.

 

[Tom Ogilvy]

Try this if the books are correct:

Sub copydata()
Dim rng1 as Range, rng2 as Range
Dim cell as Range
with Worbooks("Book1.xls").Worksheets(1)
set rng1 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup))
End With
With Workbooks("Book2.xls").Worksheets(1)
set rng2 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup))
End With
for each cell in rng1
res = application.Match(cell.Value,rng2,0)
if not iserror(res) then
rng2(res).offset(0,36).Resize(1,16).copy _
Destination:=cell.offset(0,36)
end if
Next
End Sub

If that doesn't work, then try changing the books.

 

[Guest]

That worked! Thanks!

Try this if the books are correct:

Sub copydata()
Dim rng1 as Range, rng2 as Range
Dim cell as Range
with Worbooks("Book1.xls").Worksheets(1)
set rng1 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup))
End With
With Workbooks("Book2.xls").Worksheets(1)
set rng2 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup))
End With
for each cell in rng1
res = application.Match(cell.Value,rng2,0)
if not iserror(res) then
rng2(res).offset(0,36).Resize(1,16).copy _
Destination:=cell.offset(0,36)
end if
Next
End Sub

If that doesn't work, then try changing the books.

 

[Tom Ogilvy]

It was my error (blush).

--
Regards,
Tom Ogilvy

That worked! Thanks!