Loop find between worksheets
 

Hi,

I've a range of data in Sheet 1 in column H1-500. I've used a vlookup to
take information from Sheet 2 matching H1-500 to Column B1-700 and returning
a result in Sheet 1 . What I want to do is identify the data that isn't found
from this Vlookup and add it to the list in Sheet 1

So basically in sheet 2 take the first number in the row (b1) search H1-500
in sheet 1. If it's doent' find it- Copy and paste the row to end of Sheet 1.
(row 501)

Then move to next row in sheet 2 (b2) to do the same search again.

I would like to attempt this myself, but I'm so limited on time I can't even
think where to start.

Any help please?

Carl

Loop find between worksheets
 

just use the MATCH() function in a column in Sheet2, matchimg B1:500 with
sheet1 H1:500
any values in sheet2!B that are not in sheet1!H will be errored


"Carlos" wrote in message
...
Hi,

I've a range of data in Sheet 1 in column H1-500. I've used a vlookup to
take information from Sheet 2 matching H1-500 to Column B1-700 and
returning
a result in Sheet 1 . What I want to do is identify the data that isn't
found
from this Vlookup and add it to the list in Sheet 1

So basically in sheet 2 take the first number in the row (b1) search
H1-500
in sheet 1. If it's doent' find it- Copy and paste the row to end of Sheet
1.
(row 501)

Then move to next row in sheet 2 (b2) to do the same search again.

I would like to attempt this myself, but I'm so limited on time I can't
even
think where to start.

Any help please?

Carl

Loop find between worksheets
 

This tested OK in my set up, but try it on a copy before you run it on your
original.

Sub moveOver()
Dim lr1 As Long, lr2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Sheet1")
Set sh2 = ActiveWorkbook.Sheets("Sheet2")
lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row
For Each c In sh2.Range("B1:B" & lr2)
lr1 = sh1.Cells(Rows.Count, 8).End(xlUp).Row
If WorksheetFunction.CountIf _
(sh1.Range("H1:H" & lr1), c.Value) = 0 Then
sh1.Range("H" & lr1 + 1) = c.Value
End If
Next
End Sub




"Carlos" wrote in message
...
Hi,

I've a range of data in Sheet 1 in column H1-500. I've used a vlookup to
take information from Sheet 2 matching H1-500 to Column B1-700 and
returning
a result in Sheet 1 . What I want to do is identify the data that isn't
found
from this Vlookup and add it to the list in Sheet 1

So basically in sheet 2 take the first number in the row (b1) search
H1-500
in sheet 1. If it's doent' find it- Copy and paste the row to end of Sheet
1.
(row 501)

Then move to next row in sheet 2 (b2) to do the same search again.

I would like to attempt this myself, but I'm so limited on time I can't
even
think where to start.

Any help please?

Carl

Loop find between worksheets
 

Thanks Patrick,

That get's me out of the problem as a quick fix. I've never useed the match
function before, so thanks very much

Carl

"Patrick Molloy" wrote:

just use the MATCH() function in a column in Sheet2, matchimg B1:500 with
sheet1 H1:500
any values in sheet2!B that are not in sheet1!H will be errored


"Carlos" wrote in message
...
Hi,

I've a range of data in Sheet 1 in column H1-500. I've used a vlookup to
take information from Sheet 2 matching H1-500 to Column B1-700 and
returning
a result in Sheet 1 . What I want to do is identify the data that isn't
found
from this Vlookup and add it to the list in Sheet 1

So basically in sheet 2 take the first number in the row (b1) search
H1-500
in sheet 1. If it's doent' find it- Copy and paste the row to end of Sheet
1.
(row 501)

Then move to next row in sheet 2 (b2) to do the same search again.

I would like to attempt this myself, but I'm so limited on time I can't
even
think where to start.

Any help please?

Carl

Loop find between worksheets
 

Hi,
Thanks for spending the time on this! It's very appriciated as I'll need to
do this everymonth to the same sheet so would be good to get this tied down.

I've tried this is a test page, but it doesn't seem to do anything? It runs
through the macro ok without any errors?

Any Ideas? I just copied the code straight down. (my test code is the list
in column B on sheet 1 and list in column H on sheet 2) when I run the match
values on sheet2 there are N/A errors so some don't match.

Thanks
Carl

"JLGWhiz" wrote:

This tested OK in my set up, but try it on a copy before you run it on your
original.

Sub moveOver()
Dim lr1 As Long, lr2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Sheet1")
Set sh2 = ActiveWorkbook.Sheets("Sheet2")
lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row
For Each c In sh2.Range("B1:B" & lr2)
lr1 = sh1.Cells(Rows.Count, 8).End(xlUp).Row
If WorksheetFunction.CountIf _
(sh1.Range("H1:H" & lr1), c.Value) = 0 Then
sh1.Range("H" & lr1 + 1) = c.Value
End If
Next
End Sub




"Carlos" wrote in message
...
Hi,

I've a range of data in Sheet 1 in column H1-500. I've used a vlookup to
take information from Sheet 2 matching H1-500 to Column B1-700 and
returning
a result in Sheet 1 . What I want to do is identify the data that isn't
found
from this Vlookup and add it to the list in Sheet 1

So basically in sheet 2 take the first number in the row (b1) search
H1-500
in sheet 1. If it's doent' find it- Copy and paste the row to end of Sheet
1.
(row 501)

Then move to next row in sheet 2 (b2) to do the same search again.

I would like to attempt this myself, but I'm so limited on time I can't
even
think where to start.

Any help please?

Carl

Loop find between worksheets
 

Hi Again,

Ignore my other post, I've just gone through all your coding and understood
everything. The reason mine wasn't working is I was using Sheet 1 column B,
Sheet 2 colmn H. Testing it looks good. The one thing I need to change is I
need
to move over more then the value it's found. So I'm going to play around
with this line

sh1.Range("H" & lr1 + 1) = c.Value

Thansk again for you help, I'm still learning and when people help with
codes like this it really does further my knowledge.

Best regards
Carl
"JLGWhiz" wrote:

This tested OK in my set up, but try it on a copy before you run it on your
original.

Sub moveOver()
Dim lr1 As Long, lr2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Sheet1")
Set sh2 = ActiveWorkbook.Sheets("Sheet2")
lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row
For Each c In sh2.Range("B1:B" & lr2)
lr1 = sh1.Cells(Rows.Count, 8).End(xlUp).Row
If WorksheetFunction.CountIf _
(sh1.Range("H1:H" & lr1), c.Value) = 0 Then
sh1.Range("H" & lr1 + 1) = c.Value
End If
Next
End Sub




"Carlos" wrote in message
...
Hi,

I've a range of data in Sheet 1 in column H1-500. I've used a vlookup to
take information from Sheet 2 matching H1-500 to Column B1-700 and
returning
a result in Sheet 1 . What I want to do is identify the data that isn't
found
from this Vlookup and add it to the list in Sheet 1

So basically in sheet 2 take the first number in the row (b1) search
H1-500
in sheet 1. If it's doent' find it- Copy and paste the row to end of Sheet
1.
(row 501)

Then move to next row in sheet 2 (b2) to do the same search again.

I would like to attempt this myself, but I'm so limited on time I can't
even
think where to start.

Any help please?

Carl

Loop find between worksheets
 

Didn't you say that your data was in column B on Sheet1 and in column H
on Sheet2? JLGWhiz's code was written on that assumption.

Keiji

Carlos wrote:
Hi,
Thanks for spending the time on this! It's very appriciated as I'll need to
do this everymonth to the same sheet so would be good to get this tied down.

I've tried this is a test page, but it doesn't seem to do anything? It runs
through the macro ok without any errors?

Any Ideas? I just copied the code straight down. (my test code is the list
in column B on sheet 1 and list in column H on sheet 2) when I run the match
values on sheet2 there are N/A errors so some don't match.

Thanks
Carl

"JLGWhiz" wrote:

This tested OK in my set up, but try it on a copy before you run it on your
original.

Sub moveOver()
Dim lr1 As Long, lr2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Sheet1")
Set sh2 = ActiveWorkbook.Sheets("Sheet2")
lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row
For Each c In sh2.Range("B1:B" & lr2)
lr1 = sh1.Cells(Rows.Count, 8).End(xlUp).Row
If WorksheetFunction.CountIf _
(sh1.Range("H1:H" & lr1), c.Value) = 0 Then
sh1.Range("H" & lr1 + 1) = c.Value
End If
Next
End Sub




"Carlos" wrote in message
...
Hi,

I've a range of data in Sheet 1 in column H1-500. I've used a vlookup to
take information from Sheet 2 matching H1-500 to Column B1-700 and
returning
a result in Sheet 1 . What I want to do is identify the data that isn't
found
from this Vlookup and add it to the list in Sheet 1

So basically in sheet 2 take the first number in the row (b1) search
H1-500
in sheet 1. If it's doent' find it- Copy and paste the row to end of Sheet
1.
(row 501)

Then move to next row in sheet 2 (b2) to do the same search again.

I would like to attempt this myself, but I'm so limited on time I can't
even
think where to start.

Any help please?

Carl

Loop find between worksheets
 

Just to let you know that I crack it to work on my book

Code i used was this.

Sub moveOver3()
Dim lr1 As Long, lr2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Provisions")
Set sh2 = ActiveWorkbook.Sheets("Sheet1")

lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row
For Each c In sh2.Range("B3:B" & lr2)
lr1 = sh1.Cells(Rows.Count, 6).End(xlUp).Row
If WorksheetFunction.CountIf _
(sh1.Range("F1:F" & lr1), c.Value) = 0 Then

sh1.Range("F" & lr1 + 1) = c.EntireRow.Cells(1, 2)
sh1.Range("G" & lr1 + 1) = c.EntireRow.Cells(1, 3)
sh1.Range("AB" & lr1 + 1) = c.EntireRow.Cells(1, 5)
sh1.Range("A" & lr1 + 1) = "From Last Month"
End If
Next
End Sub

So thank you very much for your help!! I really appricate it!

Carl

Loop find between worksheets
 

Glad you worked it out and thanks for sharing the results.


"Carlos" wrote in message
...
Just to let you know that I crack it to work on my book

Code i used was this.

Sub moveOver3()
Dim lr1 As Long, lr2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Provisions")
Set sh2 = ActiveWorkbook.Sheets("Sheet1")

lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row
For Each c In sh2.Range("B3:B" & lr2)
lr1 = sh1.Cells(Rows.Count, 6).End(xlUp).Row
If WorksheetFunction.CountIf _
(sh1.Range("F1:F" & lr1), c.Value) = 0 Then

sh1.Range("F" & lr1 + 1) = c.EntireRow.Cells(1, 2)
sh1.Range("G" & lr1 + 1) = c.EntireRow.Cells(1, 3)
sh1.Range("AB" & lr1 + 1) = c.EntireRow.Cells(1, 5)
sh1.Range("A" & lr1 + 1) = "From Last Month"
End If
Next
End Sub

So thank you very much for your help!! I really appricate it!

Carl