Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
Find Loop and then Loop again | Excel Programming | |||
Loops to find blanks then loop to find populated | Excel Programming |