VB question
 

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

VB question
 

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)


"Marie" wrote in message
...
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

VB question
 

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.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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)


"Marie" wrote in message
...
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

VB question
 

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" wrote:

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.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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)


"Marie" wrote in message
...
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

VB question
 

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.

--
Regards,
Tom Ogilvy


"Marie" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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)


"Marie" wrote in message
...
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

VB question
 

That worked! Thanks!

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Marie" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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)


"Marie" wrote in message
...
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

VB question
 

It was my error (blush).

--
Regards,
Tom Ogilvy

"Marie" wrote in message
...
That worked! Thanks!

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Marie" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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)


"Marie" wrote in message
...
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(xlu p))
End With
With Workbooks("Book2.xls").Worksheets(1)
set rng2 =
..Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlu p))
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