Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
I have this macro that compares values in two columns (A & M) in the same
sheet and if there is a match, it takes the value in the adjacent column (N) and pastes it into ColumnE. Sub MatchAandM() Dim Lrow As Long Dim RowCount As Long Dim xRng As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lrow FindVal = Range("A" & RowCount) Set xRng = Columns("M:M").Find(What:=FindVal, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not xRng Is Nothing Then xRng.Offset(0, 1).Copy xRng.Offset(0, -8) End If Next RowCount End Sub Works great!! Now, what Im trying to do match numbers in Column E in Sheet Sheet1 ActiveWorkbook with numbers in ColumnA of WorkBook named MarketPrices and Sheet named MarketPrices, and if there is a match, copy paste the value from ColumnB of MarketPrices to ColumnE of the Sheet1 ActiveWorkbook. Make sense? To make this just a tad harder, both files are stored on SharePoint!! Heres my (non-working) code for moving the data between the two WorkBooks: Sub MoveData() Dim SSh As Worksheet 'source sheet Dim DSh As Worksheet 'target sheet Dim LastRow As Long Dim CopyRange As String Set DSh = ActiveWorkbook.ActiveSheet 'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary") Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" & Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) & "MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")" LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row SSh.Range("B2:B" & LastRow).Copy LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues) End Sub The code fails on this line: Set SSh = Run Time error 424 Object required. I guess VBA is not recognizing the workbook, or sheet, or even range. I cant figure out the problem with the object not found. Once that is resolved, I need to use the basic logic form the first macro and incorporate it into the second macro. Any ideas? Thanks so much! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
Actually, I stand corrected. As I look at the first Sub, I now see that it
pulls the matched-value over and places it in Column E on the same row. What I want is the VBA equivalent of Vlookup, or Index/Match. So the value to the right of the matched value will not necessarily be copied/pasted to the same row; if it was the same row it would be pure coincidence. The source and destination will be different workbooks. Thanks! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: I have this macro that compares values in two columns (A & M) in the same sheet and if there is a match, it takes the value in the adjacent column (N) and pastes it into ColumnE. Sub MatchAandM() Dim Lrow As Long Dim RowCount As Long Dim xRng As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lrow FindVal = Range("A" & RowCount) Set xRng = Columns("M:M").Find(What:=FindVal, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not xRng Is Nothing Then xRng.Offset(0, 1).Copy xRng.Offset(0, -8) End If Next RowCount End Sub Works great!! Now, what Im trying to do match numbers in Column E in Sheet Sheet1 ActiveWorkbook with numbers in ColumnA of WorkBook named MarketPrices and Sheet named MarketPrices, and if there is a match, copy paste the value from ColumnB of MarketPrices to ColumnE of the Sheet1 ActiveWorkbook. Make sense? To make this just a tad harder, both files are stored on SharePoint!! Heres my (non-working) code for moving the data between the two WorkBooks: Sub MoveData() Dim SSh As Worksheet 'source sheet Dim DSh As Worksheet 'target sheet Dim LastRow As Long Dim CopyRange As String Set DSh = ActiveWorkbook.ActiveSheet 'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary") Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" & Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) & "MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")" LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row SSh.Range("B2:B" & LastRow).Copy LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues) End Sub The code fails on this line: Set SSh = Run Time error 424 Object required. I guess VBA is not recognizing the workbook, or sheet, or even range. I cant figure out the problem with the object not found. Once that is resolved, I need to use the basic logic form the first macro and incorporate it into the second macro. Any ideas? Thanks so much! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
Try the below in VBA...
Worksheetfunction.Vlookup() -- Jacob (MVP - Excel) "ryguy7272" wrote: Actually, I stand corrected. As I look at the first Sub, I now see that it pulls the matched-value over and places it in Column E on the same row. What I want is the VBA equivalent of Vlookup, or Index/Match. So the value to the right of the matched value will not necessarily be copied/pasted to the same row; if it was the same row it would be pure coincidence. The source and destination will be different workbooks. Thanks! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: I have this macro that compares values in two columns (A & M) in the same sheet and if there is a match, it takes the value in the adjacent column (N) and pastes it into ColumnE. Sub MatchAandM() Dim Lrow As Long Dim RowCount As Long Dim xRng As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lrow FindVal = Range("A" & RowCount) Set xRng = Columns("M:M").Find(What:=FindVal, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not xRng Is Nothing Then xRng.Offset(0, 1).Copy xRng.Offset(0, -8) End If Next RowCount End Sub Works great!! Now, what Im trying to do match numbers in Column E in Sheet Sheet1 ActiveWorkbook with numbers in ColumnA of WorkBook named MarketPrices and Sheet named MarketPrices, and if there is a match, copy paste the value from ColumnB of MarketPrices to ColumnE of the Sheet1 ActiveWorkbook. Make sense? To make this just a tad harder, both files are stored on SharePoint!! Heres my (non-working) code for moving the data between the two WorkBooks: Sub MoveData() Dim SSh As Worksheet 'source sheet Dim DSh As Worksheet 'target sheet Dim LastRow As Long Dim CopyRange As String Set DSh = ActiveWorkbook.ActiveSheet 'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary") Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" & Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) & "MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")" LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row SSh.Range("B2:B" & LastRow).Copy LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues) End Sub The code fails on this line: Set SSh = Run Time error 424 Object required. I guess VBA is not recognizing the workbook, or sheet, or even range. I cant figure out the problem with the object not found. Once that is resolved, I need to use the basic logic form the first macro and incorporate it into the second macro. Any ideas? Thanks so much! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
Thanks Jacob. I think I'm getting kind of close now. This is what I've got
so far: Sheet1: CUSIP a w 111123 111124 111125 111126 Sheet2: t 1 g 1 h 1 y 1 111123 12 111124 13 111125 14 111126 15 w 1 I want to pull in the 12, 13, 14, and 15, into the appropriate row on Sheet1. Sub CopyOver() 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 lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Worksheets("Sheet2").Range("A1:C" & lr2).Cells sh1.Range("B" & lr1 + 1) = Application.WorksheetFunction.VLookup(c, MktPrice, 2, False) Next c End Sub Right now I get an error on this line: sh1.Range("B" & lr1 + 1) . . . Error mssg is: run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class MktPrice is a NamedRange, but I would ultimately like to identify a used range, similar to this: ..Range("A1:C" & lr2).Cells Finally, ultimately sh1 and sh2 will be in two different workbooks. I've seen a few examples of how to do that online, but not sure how to set it up. I figured I'd try to get the 2-sheets-in-the-same-workbook concept working first . . . If someone can help me get this setup for two different workbooks, Id love to see that now! Finally, does the Worksheetfunction.Vlookup() have the same requirements as the =vlookup() function? Specifically, table_array is two or more columns of data that is sorted in ascending order. Im not 100% sure this will always be the way the data comes through. I would prefer to use Index/Match in VBA, if there is such a thing, or if Worksheetfunction.Vlookup() doesnt have the data is sorted in ascending order limitation. Thanks for everything!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jacob Skaria" wrote: Try the below in VBA... Worksheetfunction.Vlookup() -- Jacob (MVP - Excel) "ryguy7272" wrote: Actually, I stand corrected. As I look at the first Sub, I now see that it pulls the matched-value over and places it in Column E on the same row. What I want is the VBA equivalent of Vlookup, or Index/Match. So the value to the right of the matched value will not necessarily be copied/pasted to the same row; if it was the same row it would be pure coincidence. The source and destination will be different workbooks. Thanks! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: I have this macro that compares values in two columns (A & M) in the same sheet and if there is a match, it takes the value in the adjacent column (N) and pastes it into ColumnE. Sub MatchAandM() Dim Lrow As Long Dim RowCount As Long Dim xRng As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lrow FindVal = Range("A" & RowCount) Set xRng = Columns("M:M").Find(What:=FindVal, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not xRng Is Nothing Then xRng.Offset(0, 1).Copy xRng.Offset(0, -8) End If Next RowCount End Sub Works great!! Now, what Im trying to do match numbers in Column E in Sheet Sheet1 ActiveWorkbook with numbers in ColumnA of WorkBook named MarketPrices and Sheet named MarketPrices, and if there is a match, copy paste the value from ColumnB of MarketPrices to ColumnE of the Sheet1 ActiveWorkbook. Make sense? To make this just a tad harder, both files are stored on SharePoint!! Heres my (non-working) code for moving the data between the two WorkBooks: Sub MoveData() Dim SSh As Worksheet 'source sheet Dim DSh As Worksheet 'target sheet Dim LastRow As Long Dim CopyRange As String Set DSh = ActiveWorkbook.ActiveSheet 'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary") Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" & Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) & "MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")" LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row SSh.Range("B2:B" & LastRow).Copy LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues) End Sub The code fails on this line: Set SSh = Run Time error 424 Object required. I guess VBA is not recognizing the workbook, or sheet, or even range. I cant figure out the problem with the object not found. Once that is resolved, I need to use the basic logic form the first macro and incorporate it into the second macro. Any ideas? Thanks so much! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
This is a bit confusing, but I think this is pretty close:
Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Book20").Sheets("Sheet2").Range("A1:B50 "), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub An error occurs he lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row Error mssg is 'Run-time error 9: subscript out of range' I guess the reference is not fully qualified, but it seems right to me . . . but something is still wrong. During my research of this, I found out that when you use vlookup in VBA, you can't access a closed workbook. So, I'm forcing that WB to open, then do the lookup, then close ONLY that 'Book20' NOT the WB that I'm running the code from. Can someone please get me back on track with this. Thanks so much!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks Jacob. I think I'm getting kind of close now. This is what I've got so far: Sheet1: CUSIP a w 111123 111124 111125 111126 Sheet2: t 1 g 1 h 1 y 1 111123 12 111124 13 111125 14 111126 15 w 1 I want to pull in the 12, 13, 14, and 15, into the appropriate row on Sheet1. Sub CopyOver() 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 lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Worksheets("Sheet2").Range("A1:C" & lr2).Cells sh1.Range("B" & lr1 + 1) = Application.WorksheetFunction.VLookup(c, MktPrice, 2, False) Next c End Sub Right now I get an error on this line: sh1.Range("B" & lr1 + 1) . . . Error mssg is: run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class MktPrice is a NamedRange, but I would ultimately like to identify a used range, similar to this: .Range("A1:C" & lr2).Cells Finally, ultimately sh1 and sh2 will be in two different workbooks. I've seen a few examples of how to do that online, but not sure how to set it up. I figured I'd try to get the 2-sheets-in-the-same-workbook concept working first . . . If someone can help me get this setup for two different workbooks, Id love to see that now! Finally, does the Worksheetfunction.Vlookup() have the same requirements as the =vlookup() function? Specifically, table_array is two or more columns of data that is sorted in ascending order. Im not 100% sure this will always be the way the data comes through. I would prefer to use Index/Match in VBA, if there is such a thing, or if Worksheetfunction.Vlookup() doesnt have the data is sorted in ascending order limitation. Thanks for everything!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jacob Skaria" wrote: Try the below in VBA... Worksheetfunction.Vlookup() -- Jacob (MVP - Excel) "ryguy7272" wrote: Actually, I stand corrected. As I look at the first Sub, I now see that it pulls the matched-value over and places it in Column E on the same row. What I want is the VBA equivalent of Vlookup, or Index/Match. So the value to the right of the matched value will not necessarily be copied/pasted to the same row; if it was the same row it would be pure coincidence. The source and destination will be different workbooks. Thanks! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: I have this macro that compares values in two columns (A & M) in the same sheet and if there is a match, it takes the value in the adjacent column (N) and pastes it into ColumnE. Sub MatchAandM() Dim Lrow As Long Dim RowCount As Long Dim xRng As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lrow FindVal = Range("A" & RowCount) Set xRng = Columns("M:M").Find(What:=FindVal, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not xRng Is Nothing Then xRng.Offset(0, 1).Copy xRng.Offset(0, -8) End If Next RowCount End Sub Works great!! Now, what Im trying to do match numbers in Column E in Sheet Sheet1 ActiveWorkbook with numbers in ColumnA of WorkBook named MarketPrices and Sheet named MarketPrices, and if there is a match, copy paste the value from ColumnB of MarketPrices to ColumnE of the Sheet1 ActiveWorkbook. Make sense? To make this just a tad harder, both files are stored on SharePoint!! Heres my (non-working) code for moving the data between the two WorkBooks: Sub MoveData() Dim SSh As Worksheet 'source sheet Dim DSh As Worksheet 'target sheet Dim LastRow As Long Dim CopyRange As String Set DSh = ActiveWorkbook.ActiveSheet 'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary") Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" & Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) & "MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")" LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row SSh.Range("B2:B" & LastRow).Copy LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues) End Sub The code fails on this line: Set SSh = Run Time error 424 Object required. I guess VBA is not recognizing the workbook, or sheet, or even range. I cant figure out the problem with the object not found. Once that is resolved, I need to use the basic logic form the first macro and incorporate it into the second macro. Any ideas? Thanks so much! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
I made a few changes and actually made some progress on this, but now Im
stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? Thanks!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: This is a bit confusing, but I think this is pretty close: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Book20").Sheets("Sheet2").Range("A1:B50 "), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub An error occurs he lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row Error mssg is 'Run-time error 9: subscript out of range' I guess the reference is not fully qualified, but it seems right to me . . . but something is still wrong. During my research of this, I found out that when you use vlookup in VBA, you can't access a closed workbook. So, I'm forcing that WB to open, then do the lookup, then close ONLY that 'Book20' NOT the WB that I'm running the code from. Can someone please get me back on track with this. Thanks so much!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks Jacob. I think I'm getting kind of close now. This is what I've got so far: Sheet1: CUSIP a w 111123 111124 111125 111126 Sheet2: t 1 g 1 h 1 y 1 111123 12 111124 13 111125 14 111126 15 w 1 I want to pull in the 12, 13, 14, and 15, into the appropriate row on Sheet1. Sub CopyOver() 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 lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Worksheets("Sheet2").Range("A1:C" & lr2).Cells sh1.Range("B" & lr1 + 1) = Application.WorksheetFunction.VLookup(c, MktPrice, 2, False) Next c End Sub Right now I get an error on this line: sh1.Range("B" & lr1 + 1) . . . Error mssg is: run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class MktPrice is a NamedRange, but I would ultimately like to identify a used range, similar to this: .Range("A1:C" & lr2).Cells Finally, ultimately sh1 and sh2 will be in two different workbooks. I've seen a few examples of how to do that online, but not sure how to set it up. I figured I'd try to get the 2-sheets-in-the-same-workbook concept working first . . . If someone can help me get this setup for two different workbooks, Id love to see that now! Finally, does the Worksheetfunction.Vlookup() have the same requirements as the =vlookup() function? Specifically, table_array is two or more columns of data that is sorted in ascending order. Im not 100% sure this will always be the way the data comes through. I would prefer to use Index/Match in VBA, if there is such a thing, or if Worksheetfunction.Vlookup() doesnt have the data is sorted in ascending order limitation. Thanks for everything!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jacob Skaria" wrote: Try the below in VBA... Worksheetfunction.Vlookup() -- Jacob (MVP - Excel) "ryguy7272" wrote: Actually, I stand corrected. As I look at the first Sub, I now see that it pulls the matched-value over and places it in Column E on the same row. What I want is the VBA equivalent of Vlookup, or Index/Match. So the value to the right of the matched value will not necessarily be copied/pasted to the same row; if it was the same row it would be pure coincidence. The source and destination will be different workbooks. Thanks! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: I have this macro that compares values in two columns (A & M) in the same sheet and if there is a match, it takes the value in the adjacent column (N) and pastes it into ColumnE. Sub MatchAandM() Dim Lrow As Long Dim RowCount As Long Dim xRng As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lrow FindVal = Range("A" & RowCount) Set xRng = Columns("M:M").Find(What:=FindVal, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not xRng Is Nothing Then xRng.Offset(0, 1).Copy xRng.Offset(0, -8) End If Next RowCount End Sub Works great!! Now, what Im trying to do match numbers in Column E in Sheet Sheet1 ActiveWorkbook with numbers in ColumnA of WorkBook named MarketPrices and Sheet named MarketPrices, and if there is a match, copy paste the value from ColumnB of MarketPrices to ColumnE of the Sheet1 ActiveWorkbook. Make sense? To make this just a tad harder, both files are stored on SharePoint!! Heres my (non-working) code for moving the data between the two WorkBooks: Sub MoveData() Dim SSh As Worksheet 'source sheet Dim DSh As Worksheet 'target sheet Dim LastRow As Long Dim CopyRange As String Set DSh = ActiveWorkbook.ActiveSheet 'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary") Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" & Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) & "MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")" LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row SSh.Range("B2:B" & LastRow).Copy LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues) End Sub The code fails on this line: Set SSh = Run Time error 424 Object required. I guess VBA is not recognizing the workbook, or sheet, or even range. I cant figure out the problem with the object not found. Once that is resolved, I need to use the basic logic form the first macro and incorporate it into the second macro. Any ideas? Thanks so much! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
Hi
Not sure I understand exactly what you are trying to do, but I think you have data in column A of sheet1, and want to look up those values from the first column of Sheet2 and return the value that exists in column B for Sheet2. If so then you need something like this for your ranges and Vlookups You will need to dim myrng2 as Range Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng2 to the used range in columns ' A and B on sheet2. lr2 count of column A Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng to the used range in column ' A on sheet1, lr1 being change to a count of ' Column A Set myRng = sh1.Range("A1:A" & _ lr1) 'set lr1 back to 1 to start on first row of sheet1 lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c The Countif function is there to test whether the value to be looked up exists in the rnage first, otherwise you will get an error 1004 if it does not exist. I'm not sure about setting lr1 as the count of rows in column B of sheet1 as you had it. If you run the code a second time, the results would be placed in rows below where the results occurred the first time. I think lr1 needs to set to 1 before you enter the loop, but I may have misinterpreted what you are trying to do. -- Regards Roger Govier "ryguy7272" wrote in message ... I made a few changes and actually made some progress on this, but now Im stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? Thanks!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: This is a bit confusing, but I think this is pretty close: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Book20").Sheets("Sheet2").Range("A1:B50 "), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub An error occurs he lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row Error mssg is 'Run-time error 9: subscript out of range' I guess the reference is not fully qualified, but it seems right to me . . . but something is still wrong. During my research of this, I found out that when you use vlookup in VBA, you can't access a closed workbook. So, I'm forcing that WB to open, then do the lookup, then close ONLY that 'Book20' NOT the WB that I'm running the code from. Can someone please get me back on track with this. Thanks so much!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks Jacob. I think I'm getting kind of close now. This is what I've got so far: Sheet1: CUSIP a w 111123 111124 111125 111126 Sheet2: t 1 g 1 h 1 y 1 111123 12 111124 13 111125 14 111126 15 w 1 I want to pull in the 12, 13, 14, and 15, into the appropriate row on Sheet1. Sub CopyOver() 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 lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Worksheets("Sheet2").Range("A1:C" & lr2).Cells sh1.Range("B" & lr1 + 1) = Application.WorksheetFunction.VLookup(c, MktPrice, 2, False) Next c End Sub Right now I get an error on this line: sh1.Range("B" & lr1 + 1) . . . Error mssg is: run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class MktPrice is a NamedRange, but I would ultimately like to identify a used range, similar to this: .Range("A1:C" & lr2).Cells Finally, ultimately sh1 and sh2 will be in two different workbooks. I've seen a few examples of how to do that online, but not sure how to set it up. I figured I'd try to get the 2-sheets-in-the-same-workbook concept working first . . . If someone can help me get this setup for two different workbooks, Id love to see that now! Finally, does the Worksheetfunction.Vlookup() have the same requirements as the =vlookup() function? Specifically, table_array is two or more columns of data that is sorted in ascending order. Im not 100% sure this will always be the way the data comes through. I would prefer to use Index/Match in VBA, if there is such a thing, or if Worksheetfunction.Vlookup() doesnt have the data is sorted in ascending order limitation. Thanks for everything!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jacob Skaria" wrote: Try the below in VBA... Worksheetfunction.Vlookup() -- Jacob (MVP - Excel) "ryguy7272" wrote: Actually, I stand corrected. As I look at the first Sub, I now see that it pulls the matched-value over and places it in Column E on the same row. What I want is the VBA equivalent of Vlookup, or Index/Match. So the value to the right of the matched value will not necessarily be copied/pasted to the same row; if it was the same row it would be pure coincidence. The source and destination will be different workbooks. Thanks! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: I have this macro that compares values in two columns (A & M) in the same sheet and if there is a match, it takes the value in the adjacent column (N) and pastes it into ColumnE. Sub MatchAandM() Dim Lrow As Long Dim RowCount As Long Dim xRng As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lrow FindVal = Range("A" & RowCount) Set xRng = Columns("M:M").Find(What:=FindVal, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not xRng Is Nothing Then xRng.Offset(0, 1).Copy xRng.Offset(0, -8) End If Next RowCount End Sub Works great!! Now, what Im trying to do match numbers in Column E in Sheet Sheet1 ActiveWorkbook with numbers in ColumnA of WorkBook named MarketPrices and Sheet named MarketPrices, and if there is a match, copy paste the value from ColumnB of MarketPrices to ColumnE of the Sheet1 ActiveWorkbook. Make sense? To make this just a tad harder, both files are stored on SharePoint!! Heres my (non-working) code for moving the data between the two WorkBooks: Sub MoveData() Dim SSh As Worksheet 'source sheet Dim DSh As Worksheet 'target sheet Dim LastRow As Long Dim CopyRange As String Set DSh = ActiveWorkbook.ActiveSheet 'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary") Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" & Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) & "MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")" LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row SSh.Range("B2:B" & LastRow).Copy LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues) End Sub The code fails on this line: Set SSh = Run Time error 424 Object required. I guess VBA is not recognizing the workbook, or sheet, or even range. I cant figure out the problem with the object not found. Once that is resolved, I need to use the basic logic form the first macro and incorporate it into the second macro. Any ideas? Thanks so much! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. __________ Information from ESET Smart Security, version of virus signature database 5173 (20100604) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 5173 (20100604) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
Sorry for the delay here. I had to take the CFA this past weekend and was
preoccupied with that, these past few days. Finally, I can revisit this project. Yes, Roger, you are exactly right! I have data in Column A of Sheet1 in the active workbook, and want to look up matches from Column A in another workbook name Test2, and when there is a match, return the value that exists in Column B. I tested your code; it looks good, it doesnt actually do anything for me. There is no error; the Test2 opens and then closes, but nothing was updated. Did I do something wrong, perhaps? I have a named range myrng2, in Test2 (from A1:B7). The code, as it is now, is below. Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myrng2 As Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "C:\Users\New User\Desktop\Briefcase\Test2.xlsm" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("A1:B10") Set sh2 = Sheets("Sheet1") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set myRng = sh1.Range("A1:A" & lr1) lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub What do I have to do to get this working? Thanks so much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Roger Govier" wrote: Hi Not sure I understand exactly what you are trying to do, but I think you have data in column A of sheet1, and want to look up those values from the first column of Sheet2 and return the value that exists in column B for Sheet2. If so then you need something like this for your ranges and Vlookups You will need to dim myrng2 as Range Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng2 to the used range in columns ' A and B on sheet2. lr2 count of column A Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng to the used range in column ' A on sheet1, lr1 being change to a count of ' Column A Set myRng = sh1.Range("A1:A" & _ lr1) 'set lr1 back to 1 to start on first row of sheet1 lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c The Countif function is there to test whether the value to be looked up exists in the rnage first, otherwise you will get an error 1004 if it does not exist. I'm not sure about setting lr1 as the count of rows in column B of sheet1 as you had it. If you run the code a second time, the results would be placed in rows below where the results occurred the first time. I think lr1 needs to set to 1 before you enter the loop, but I may have misinterpreted what you are trying to do. -- Regards Roger Govier "ryguy7272" wrote in message ... I made a few changes and actually made some progress on this, but now Im stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? Thanks!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: This is a bit confusing, but I think this is pretty close: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Book20").Sheets("Sheet2").Range("A1:B50 "), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub An error occurs he lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row Error mssg is 'Run-time error 9: subscript out of range' I guess the reference is not fully qualified, but it seems right to me . . . but something is still wrong. During my research of this, I found out that when you use vlookup in VBA, you can't access a closed workbook. So, I'm forcing that WB to open, then do the lookup, then close ONLY that 'Book20' NOT the WB that I'm running the code from. Can someone please get me back on track with this. Thanks so much!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks Jacob. I think I'm getting kind of close now. This is what I've got so far: Sheet1: CUSIP a w 111123 111124 111125 111126 Sheet2: t 1 g 1 h 1 y 1 111123 12 111124 13 111125 14 111126 15 w 1 I want to pull in the 12, 13, 14, and 15, into the appropriate row on Sheet1. Sub CopyOver() 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 lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Worksheets("Sheet2").Range("A1:C" & lr2).Cells sh1.Range("B" & lr1 + 1) = Application.WorksheetFunction.VLookup(c, MktPrice, 2, False) Next c End Sub Right now I get an error on this line: sh1.Range("B" & lr1 + 1) . . . Error mssg is: run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class MktPrice is a NamedRange, but I would ultimately like to identify a used range, similar to this: .Range("A1:C" & lr2).Cells Finally, ultimately sh1 and sh2 will be in two different workbooks. I've seen a few examples of how to do that online, but not sure how to set it up. I figured I'd try to get the 2-sheets-in-the-same-workbook concept working first . . . If someone can help me get this setup for two different workbooks, Id love to see that now! Finally, does the Worksheetfunction.Vlookup() have the same requirements as the =vlookup() function? Specifically, table_array is two or more columns of data that is sorted in ascending order. Im not 100% sure this will always be the way the data comes through. I would prefer to use Index/Match in VBA, if there is such a thing, or if Worksheetfunction.Vlookup() doesnt have the data is sorted in ascending order limitation. Thanks for everything!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jacob Skaria" wrote: Try the below in VBA... Worksheetfunction.Vlookup() -- Jacob (MVP - Excel) "ryguy7272" wrote: Actually, I stand corrected. As I look at the first Sub, I now see that it pulls the matched-value over and places it in Column E on the same row. What I want is the VBA equivalent of Vlookup, or Index/Match. So the value to the |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
Hi Ryan
You dont need to open the workbook or loop.. Try the below macro..(which I have tried.) Sub Macro() Dim rngTemp As Range, strPath As String, strFile As String strPath = "I:\Ryan\" strFile = "Book20.xls" Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:B,2,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With End Sub "ryguy7272" wrote: Sorry for the delay here. I had to take the CFA this past weekend and was preoccupied with that, these past few days. Finally, I can revisit this project. Yes, Roger, you are exactly right! I have data in Column A of Sheet1 in the active workbook, and want to look up matches from Column A in another workbook name Test2, and when there is a match, return the value that exists in Column B. I tested your code; it looks good, it doesnt actually do anything for me. There is no error; the Test2 opens and then closes, but nothing was updated. Did I do something wrong, perhaps? I have a named range myrng2, in Test2 (from A1:B7). The code, as it is now, is below. Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myrng2 As Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "C:\Users\New User\Desktop\Briefcase\Test2.xlsm" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("A1:B10") Set sh2 = Sheets("Sheet1") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set myRng = sh1.Range("A1:A" & lr1) lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub What do I have to do to get this working? Thanks so much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Roger Govier" wrote: Hi Not sure I understand exactly what you are trying to do, but I think you have data in column A of sheet1, and want to look up those values from the first column of Sheet2 and return the value that exists in column B for Sheet2. If so then you need something like this for your ranges and Vlookups You will need to dim myrng2 as Range Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng2 to the used range in columns ' A and B on sheet2. lr2 count of column A Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng to the used range in column ' A on sheet1, lr1 being change to a count of ' Column A Set myRng = sh1.Range("A1:A" & _ lr1) 'set lr1 back to 1 to start on first row of sheet1 lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c The Countif function is there to test whether the value to be looked up exists in the rnage first, otherwise you will get an error 1004 if it does not exist. I'm not sure about setting lr1 as the count of rows in column B of sheet1 as you had it. If you run the code a second time, the results would be placed in rows below where the results occurred the first time. I think lr1 needs to set to 1 before you enter the loop, but I may have misinterpreted what you are trying to do. -- Regards Roger Govier "ryguy7272" wrote in message ... I made a few changes and actually made some progress on this, but now Im stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? Thanks!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: This is a bit confusing, but I think this is pretty close: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Book20").Sheets("Sheet2").Range("A1:B50 "), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub An error occurs he lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row Error mssg is 'Run-time error 9: subscript out of range' I guess the reference is not fully qualified, but it seems right to me . . . but something is still wrong. During my research of this, I found out that when you use vlookup in VBA, you can't access a closed workbook. So, I'm forcing that WB to open, then do the lookup, then close ONLY that 'Book20' NOT the WB that I'm running the code from. Can someone please get me back on track with this. Thanks so much!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks Jacob. I think I'm getting kind of close now. This is what I've got so far: Sheet1: CUSIP a w 111123 111124 111125 111126 Sheet2: t 1 g 1 h 1 y 1 111123 12 111124 13 111125 14 111126 15 w 1 I want to pull in the 12, 13, 14, and 15, into the appropriate row on Sheet1. Sub CopyOver() 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 lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
Thanks so much Jackpot! That is really slick and it works perfect!! Just
before I read your post, I was actually just toggling back and forth b/w the Locals Window and the Immediate Window, trying to figure out why Roger's code wasn't working for me. Do you have any idea why that code would not work? This is a HUGE help! Thanks again! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: Hi Ryan You dont need to open the workbook or loop.. Try the below macro..(which I have tried.) Sub Macro() Dim rngTemp As Range, strPath As String, strFile As String strPath = "I:\Ryan\" strFile = "Book20.xls" Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:B,2,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With End Sub "ryguy7272" wrote: Sorry for the delay here. I had to take the CFA this past weekend and was preoccupied with that, these past few days. Finally, I can revisit this project. Yes, Roger, you are exactly right! I have data in Column A of Sheet1 in the active workbook, and want to look up matches from Column A in another workbook name Test2, and when there is a match, return the value that exists in Column B. I tested your code; it looks good, it doesnt actually do anything for me. There is no error; the Test2 opens and then closes, but nothing was updated. Did I do something wrong, perhaps? I have a named range myrng2, in Test2 (from A1:B7). The code, as it is now, is below. Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myrng2 As Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "C:\Users\New User\Desktop\Briefcase\Test2.xlsm" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("A1:B10") Set sh2 = Sheets("Sheet1") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set myRng = sh1.Range("A1:A" & lr1) lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub What do I have to do to get this working? Thanks so much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Roger Govier" wrote: Hi Not sure I understand exactly what you are trying to do, but I think you have data in column A of sheet1, and want to look up those values from the first column of Sheet2 and return the value that exists in column B for Sheet2. If so then you need something like this for your ranges and Vlookups You will need to dim myrng2 as Range Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng2 to the used range in columns ' A and B on sheet2. lr2 count of column A Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng to the used range in column ' A on sheet1, lr1 being change to a count of ' Column A Set myRng = sh1.Range("A1:A" & _ lr1) 'set lr1 back to 1 to start on first row of sheet1 lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c The Countif function is there to test whether the value to be looked up exists in the rnage first, otherwise you will get an error 1004 if it does not exist. I'm not sure about setting lr1 as the count of rows in column B of sheet1 as you had it. If you run the code a second time, the results would be placed in rows below where the results occurred the first time. I think lr1 needs to set to 1 before you enter the loop, but I may have misinterpreted what you are trying to do. -- Regards Roger Govier "ryguy7272" wrote in message ... I made a few changes and actually made some progress on this, but now Im stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? Thanks!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: This is a bit confusing, but I think this is pretty close: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Book20").Sheets("Sheet2").Range("A1:B50 "), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub An error occurs he lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row Error mssg is 'Run-time error 9: subscript out of range' I guess the reference is not fully qualified, but it seems right to me . . . but something is still wrong. During my research of this, I found out that when you use vlookup in VBA, you can't access a closed workbook. So, I'm forcing that WB to open, then do the lookup, then close ONLY that 'Book20' NOT the WB that I'm running the code from. Can someone please get me back on track with this. Thanks so much!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks Jacob. I think I'm getting kind of close now. This is what I've got so far: Sheet1: |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
Hi Ryan
I am not sure why it does not work for you. I tried the same in a new workbook Sheet1 and Sheet2..with sample data you posted as a response to my initial post (display name has been changed from Jacob)...It works... Sub Macro() Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set myRng = sh1.Range("A1:A" & lr1) lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, False) End If lr1 = lr1 + 1 Next c End Sub "ryguy7272" wrote: Thanks so much Jackpot! That is really slick and it works perfect!! Just before I read your post, I was actually just toggling back and forth b/w the Locals Window and the Immediate Window, trying to figure out why Roger's code wasn't working for me. Do you have any idea why that code would not work? This is a HUGE help! Thanks again! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: Hi Ryan You dont need to open the workbook or loop.. Try the below macro..(which I have tried.) Sub Macro() Dim rngTemp As Range, strPath As String, strFile As String strPath = "I:\Ryan\" strFile = "Book20.xls" Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:B,2,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With End Sub "ryguy7272" wrote: Sorry for the delay here. I had to take the CFA this past weekend and was preoccupied with that, these past few days. Finally, I can revisit this project. Yes, Roger, you are exactly right! I have data in Column A of Sheet1 in the active workbook, and want to look up matches from Column A in another workbook name Test2, and when there is a match, return the value that exists in Column B. I tested your code; it looks good, it doesnt actually do anything for me. There is no error; the Test2 opens and then closes, but nothing was updated. Did I do something wrong, perhaps? I have a named range myrng2, in Test2 (from A1:B7). The code, as it is now, is below. Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myrng2 As Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "C:\Users\New User\Desktop\Briefcase\Test2.xlsm" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("A1:B10") Set sh2 = Sheets("Sheet1") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set myRng = sh1.Range("A1:A" & lr1) lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub What do I have to do to get this working? Thanks so much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Roger Govier" wrote: Hi Not sure I understand exactly what you are trying to do, but I think you have data in column A of sheet1, and want to look up those values from the first column of Sheet2 and return the value that exists in column B for Sheet2. If so then you need something like this for your ranges and Vlookups You will need to dim myrng2 as Range Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng2 to the used range in columns ' A and B on sheet2. lr2 count of column A Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng to the used range in column ' A on sheet1, lr1 being change to a count of ' Column A Set myRng = sh1.Range("A1:A" & _ lr1) 'set lr1 back to 1 to start on first row of sheet1 lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c The Countif function is there to test whether the value to be looked up exists in the rnage first, otherwise you will get an error 1004 if it does not exist. I'm not sure about setting lr1 as the count of rows in column B of sheet1 as you had it. If you run the code a second time, the results would be placed in rows below where the results occurred the first time. I think lr1 needs to set to 1 before you enter the loop, but I may have misinterpreted what you are trying to do. -- Regards Roger Govier "ryguy7272" wrote in message ... I made a few changes and actually made some progress on this, but now Im stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? Thanks!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: This is a bit confusing, but I think this is pretty close: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Book20").Sheets("Sheet2").Range("A1:B50 "), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub An error occurs he lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row Error mssg is 'Run-time error 9: subscript out of range' I guess the reference is not fully qualified, but it seems right to me . . . but something is still wrong. During my research of this, I found out that when you use vlookup in VBA, you can't access a closed workbook. So, I'm forcing that WB to open, then do the lookup, then close ONLY that 'Book20' NOT the WB that I'm running the code from. Can someone please get me back on track with this. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
One more thing...I haven't see the data table yet, but I heard that ColB and
ColC will have data, and I need to do a vlookup on BOTH of these columns, and pull the matching values into ColB of the Active Workbook. I tried an If...Then and I tried a couple different loops but couldn't get the desired results. How can I look for matches in either ColB or ColC, and pull the results back into the ColB of the Active Workbook? I hope that's doable. Thanks again!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks so much Jackpot! That is really slick and it works perfect!! Just before I read your post, I was actually just toggling back and forth b/w the Locals Window and the Immediate Window, trying to figure out why Roger's code wasn't working for me. Do you have any idea why that code would not work? This is a HUGE help! Thanks again! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: Hi Ryan You dont need to open the workbook or loop.. Try the below macro..(which I have tried.) Sub Macro() Dim rngTemp As Range, strPath As String, strFile As String strPath = "I:\Ryan\" strFile = "Book20.xls" Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:B,2,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With End Sub "ryguy7272" wrote: Sorry for the delay here. I had to take the CFA this past weekend and was preoccupied with that, these past few days. Finally, I can revisit this project. Yes, Roger, you are exactly right! I have data in Column A of Sheet1 in the active workbook, and want to look up matches from Column A in another workbook name Test2, and when there is a match, return the value that exists in Column B. I tested your code; it looks good, it doesnt actually do anything for me. There is no error; the Test2 opens and then closes, but nothing was updated. Did I do something wrong, perhaps? I have a named range myrng2, in Test2 (from A1:B7). The code, as it is now, is below. Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myrng2 As Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "C:\Users\New User\Desktop\Briefcase\Test2.xlsm" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("A1:B10") Set sh2 = Sheets("Sheet1") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set myRng = sh1.Range("A1:A" & lr1) lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub What do I have to do to get this working? Thanks so much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Roger Govier" wrote: Hi Not sure I understand exactly what you are trying to do, but I think you have data in column A of sheet1, and want to look up those values from the first column of Sheet2 and return the value that exists in column B for Sheet2. If so then you need something like this for your ranges and Vlookups You will need to dim myrng2 as Range Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng2 to the used range in columns ' A and B on sheet2. lr2 count of column A Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng to the used range in column ' A on sheet1, lr1 being change to a count of ' Column A Set myRng = sh1.Range("A1:A" & _ lr1) 'set lr1 back to 1 to start on first row of sheet1 lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c The Countif function is there to test whether the value to be looked up exists in the rnage first, otherwise you will get an error 1004 if it does not exist. I'm not sure about setting lr1 as the count of rows in column B of sheet1 as you had it. If you run the code a second time, the results would be placed in rows below where the results occurred the first time. I think lr1 needs to set to 1 before you enter the loop, but I may have misinterpreted what you are trying to do. -- Regards Roger Govier "ryguy7272" wrote in message ... I made a few changes and actually made some progress on this, but now Im stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? Thanks!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: This is a bit confusing, but I think this is pretty close: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Book20").Sheets("Sheet2").Range("A1:B50 "), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub An error occurs he lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row Error mssg is 'Run-time error 9: subscript out of range' I guess the reference is not fully qualified, but it seems right to me . . . but something is still wrong. During my research of this, I found out that when you use vlookup in VBA, you can't access a closed workbook. So, I'm forcing that WB to open, then do the lookup, then close ONLY that 'Book20' NOT the WB that I'm running the code from. Can someone please get me back on track with this. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
Try below...(Modify to suit )
With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:C,2,0)&VLOOKUP(A1,'" & _ strPath & "[" & strFile & "]Sheet1'!A:C,3,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With "ryguy7272" wrote: One more thing...I haven't see the data table yet, but I heard that ColB and ColC will have data, and I need to do a vlookup on BOTH of these columns, and pull the matching values into ColB of the Active Workbook. I tried an If...Then and I tried a couple different loops but couldn't get the desired results. How can I look for matches in either ColB or ColC, and pull the results back into the ColB of the Active Workbook? I hope that's doable. Thanks again!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks so much Jackpot! That is really slick and it works perfect!! Just before I read your post, I was actually just toggling back and forth b/w the Locals Window and the Immediate Window, trying to figure out why Roger's code wasn't working for me. Do you have any idea why that code would not work? This is a HUGE help! Thanks again! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: Hi Ryan You dont need to open the workbook or loop.. Try the below macro..(which I have tried.) Sub Macro() Dim rngTemp As Range, strPath As String, strFile As String strPath = "I:\Ryan\" strFile = "Book20.xls" Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:B,2,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With End Sub "ryguy7272" wrote: Sorry for the delay here. I had to take the CFA this past weekend and was preoccupied with that, these past few days. Finally, I can revisit this project. Yes, Roger, you are exactly right! I have data in Column A of Sheet1 in the active workbook, and want to look up matches from Column A in another workbook name Test2, and when there is a match, return the value that exists in Column B. I tested your code; it looks good, it doesnt actually do anything for me. There is no error; the Test2 opens and then closes, but nothing was updated. Did I do something wrong, perhaps? I have a named range myrng2, in Test2 (from A1:B7). The code, as it is now, is below. Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myrng2 As Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "C:\Users\New User\Desktop\Briefcase\Test2.xlsm" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("A1:B10") Set sh2 = Sheets("Sheet1") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set myRng = sh1.Range("A1:A" & lr1) lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub What do I have to do to get this working? Thanks so much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Roger Govier" wrote: Hi Not sure I understand exactly what you are trying to do, but I think you have data in column A of sheet1, and want to look up those values from the first column of Sheet2 and return the value that exists in column B for Sheet2. If so then you need something like this for your ranges and Vlookups You will need to dim myrng2 as Range Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng2 to the used range in columns ' A and B on sheet2. lr2 count of column A Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng to the used range in column ' A on sheet1, lr1 being change to a count of ' Column A Set myRng = sh1.Range("A1:A" & _ lr1) 'set lr1 back to 1 to start on first row of sheet1 lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c The Countif function is there to test whether the value to be looked up exists in the rnage first, otherwise you will get an error 1004 if it does not exist. I'm not sure about setting lr1 as the count of rows in column B of sheet1 as you had it. If you run the code a second time, the results would be placed in rows below where the results occurred the first time. I think lr1 needs to set to 1 before you enter the loop, but I may have misinterpreted what you are trying to do. -- Regards Roger Govier "ryguy7272" wrote in message ... I made a few changes and actually made some progress on this, but now Im stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? Thanks!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: This is a bit confusing, but I think this is pretty close: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Book20").Sheets("Sheet2").Range("A1:B50 "), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
Perfect!!! I was like, where did 'Jackpot' come from? Now it makes sense.
Jacob, thanks for this, and all the other things too!! I appreciate it SOOOOO much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: Try below...(Modify to suit ) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:C,2,0)&VLOOKUP(A1,'" & _ strPath & "[" & strFile & "]Sheet1'!A:C,3,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With "ryguy7272" wrote: One more thing...I haven't see the data table yet, but I heard that ColB and ColC will have data, and I need to do a vlookup on BOTH of these columns, and pull the matching values into ColB of the Active Workbook. I tried an If...Then and I tried a couple different loops but couldn't get the desired results. How can I look for matches in either ColB or ColC, and pull the results back into the ColB of the Active Workbook? I hope that's doable. Thanks again!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks so much Jackpot! That is really slick and it works perfect!! Just before I read your post, I was actually just toggling back and forth b/w the Locals Window and the Immediate Window, trying to figure out why Roger's code wasn't working for me. Do you have any idea why that code would not work? This is a HUGE help! Thanks again! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: Hi Ryan You dont need to open the workbook or loop.. Try the below macro..(which I have tried.) Sub Macro() Dim rngTemp As Range, strPath As String, strFile As String strPath = "I:\Ryan\" strFile = "Book20.xls" Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:B,2,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With End Sub "ryguy7272" wrote: Sorry for the delay here. I had to take the CFA this past weekend and was preoccupied with that, these past few days. Finally, I can revisit this project. Yes, Roger, you are exactly right! I have data in Column A of Sheet1 in the active workbook, and want to look up matches from Column A in another workbook name Test2, and when there is a match, return the value that exists in Column B. I tested your code; it looks good, it doesnt actually do anything for me. There is no error; the Test2 opens and then closes, but nothing was updated. Did I do something wrong, perhaps? I have a named range myrng2, in Test2 (from A1:B7). The code, as it is now, is below. Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myrng2 As Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "C:\Users\New User\Desktop\Briefcase\Test2.xlsm" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("A1:B10") Set sh2 = Sheets("Sheet1") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set myRng = sh1.Range("A1:A" & lr1) lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub What do I have to do to get this working? Thanks so much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Roger Govier" wrote: Hi Not sure I understand exactly what you are trying to do, but I think you have data in column A of sheet1, and want to look up those values from the first column of Sheet2 and return the value that exists in column B for Sheet2. If so then you need something like this for your ranges and Vlookups You will need to dim myrng2 as Range Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng2 to the used range in columns ' A and B on sheet2. lr2 count of column A Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng to the used range in column ' A on sheet1, lr1 being change to a count of ' Column A Set myRng = sh1.Range("A1:A" & _ lr1) 'set lr1 back to 1 to start on first row of sheet1 lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c The Countif function is there to test whether the value to be looked up exists in the rnage first, otherwise you will get an error 1004 if it does not exist. I'm not sure about setting lr1 as the count of rows in column B of sheet1 as you had it. If you run the code a second time, the results would be placed in rows below where the results occurred the first time. I think lr1 needs to set to 1 before you enter the loop, but I may have misinterpreted what you are trying to do. -- Regards Roger Govier "ryguy7272" wrote in message ... I made a few changes and actually made some progress on this, but now Im stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? Thanks!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: This is a bit confusing, but I think this is pretty close: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" & lr2).Cells |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
You are most welcome and thanks for the feedback.
"ryguy7272" wrote: Perfect!!! I was like, where did 'Jackpot' come from? Now it makes sense. Jacob, thanks for this, and all the other things too!! I appreciate it SOOOOO much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: Try below...(Modify to suit ) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:C,2,0)&VLOOKUP(A1,'" & _ strPath & "[" & strFile & "]Sheet1'!A:C,3,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With "ryguy7272" wrote: One more thing...I haven't see the data table yet, but I heard that ColB and ColC will have data, and I need to do a vlookup on BOTH of these columns, and pull the matching values into ColB of the Active Workbook. I tried an If...Then and I tried a couple different loops but couldn't get the desired results. How can I look for matches in either ColB or ColC, and pull the results back into the ColB of the Active Workbook? I hope that's doable. Thanks again!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks so much Jackpot! That is really slick and it works perfect!! Just before I read your post, I was actually just toggling back and forth b/w the Locals Window and the Immediate Window, trying to figure out why Roger's code wasn't working for me. Do you have any idea why that code would not work? This is a HUGE help! Thanks again! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: Hi Ryan You dont need to open the workbook or loop.. Try the below macro..(which I have tried.) Sub Macro() Dim rngTemp As Range, strPath As String, strFile As String strPath = "I:\Ryan\" strFile = "Book20.xls" Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:B,2,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With End Sub "ryguy7272" wrote: Sorry for the delay here. I had to take the CFA this past weekend and was preoccupied with that, these past few days. Finally, I can revisit this project. Yes, Roger, you are exactly right! I have data in Column A of Sheet1 in the active workbook, and want to look up matches from Column A in another workbook name Test2, and when there is a match, return the value that exists in Column B. I tested your code; it looks good, it doesnt actually do anything for me. There is no error; the Test2 opens and then closes, but nothing was updated. Did I do something wrong, perhaps? I have a named range myrng2, in Test2 (from A1:B7). The code, as it is now, is below. Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myrng2 As Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "C:\Users\New User\Desktop\Briefcase\Test2.xlsm" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("A1:B10") Set sh2 = Sheets("Sheet1") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set myRng = sh1.Range("A1:A" & lr1) lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub What do I have to do to get this working? Thanks so much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Roger Govier" wrote: Hi Not sure I understand exactly what you are trying to do, but I think you have data in column A of sheet1, and want to look up those values from the first column of Sheet2 and return the value that exists in column B for Sheet2. If so then you need something like this for your ranges and Vlookups You will need to dim myrng2 as Range Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng2 to the used range in columns ' A and B on sheet2. lr2 count of column A Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng to the used range in column ' A on sheet1, lr1 being change to a count of ' Column A Set myRng = sh1.Range("A1:A" & _ lr1) 'set lr1 back to 1 to start on first row of sheet1 lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c The Countif function is there to test whether the value to be looked up exists in the rnage first, otherwise you will get an error 1004 if it does not exist. I'm not sure about setting lr1 as the count of rows in column B of sheet1 as you had it. If you run the code a second time, the results would be placed in rows below where the results occurred the first time. I think lr1 needs to set to 1 before you enter the loop, but I may have misinterpreted what you are trying to do. -- Regards Roger Govier "ryguy7272" wrote in message ... I made a few changes and actually made some progress on this, but now Im stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? Thanks!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: This is a bit confusing, but I think this is pretty close: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
Ummmm, one more thing, Jacob. I put some test data in A & B & C, down to row
65536 (just on excel 2003 now but will upgrade to 2007 very soon). I get an error on this line: Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row) The error reads: Run-time error 1004 Method Range of object _Global failed. I tried a couple things, including naming the range, and then I tried this: Set rngTemp = Range(FullRange) Still getting the same error. Ultimately, this project may require 300,000 rows Ugh! What now? -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: You are most welcome and thanks for the feedback. "ryguy7272" wrote: Perfect!!! I was like, where did 'Jackpot' come from? Now it makes sense. Jacob, thanks for this, and all the other things too!! I appreciate it SOOOOO much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: Try below...(Modify to suit ) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:C,2,0)&VLOOKUP(A1,'" & _ strPath & "[" & strFile & "]Sheet1'!A:C,3,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With "ryguy7272" wrote: One more thing...I haven't see the data table yet, but I heard that ColB and ColC will have data, and I need to do a vlookup on BOTH of these columns, and pull the matching values into ColB of the Active Workbook. I tried an If...Then and I tried a couple different loops but couldn't get the desired results. How can I look for matches in either ColB or ColC, and pull the results back into the ColB of the Active Workbook? I hope that's doable. Thanks again!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks so much Jackpot! That is really slick and it works perfect!! Just before I read your post, I was actually just toggling back and forth b/w the Locals Window and the Immediate Window, trying to figure out why Roger's code wasn't working for me. Do you have any idea why that code would not work? This is a HUGE help! Thanks again! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: Hi Ryan You dont need to open the workbook or loop.. Try the below macro..(which I have tried.) Sub Macro() Dim rngTemp As Range, strPath As String, strFile As String strPath = "I:\Ryan\" strFile = "Book20.xls" Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:B,2,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With End Sub "ryguy7272" wrote: Sorry for the delay here. I had to take the CFA this past weekend and was preoccupied with that, these past few days. Finally, I can revisit this project. Yes, Roger, you are exactly right! I have data in Column A of Sheet1 in the active workbook, and want to look up matches from Column A in another workbook name Test2, and when there is a match, return the value that exists in Column B. I tested your code; it looks good, it doesnt actually do anything for me. There is no error; the Test2 opens and then closes, but nothing was updated. Did I do something wrong, perhaps? I have a named range myrng2, in Test2 (from A1:B7). The code, as it is now, is below. Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myrng2 As Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "C:\Users\New User\Desktop\Briefcase\Test2.xlsm" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("A1:B10") Set sh2 = Sheets("Sheet1") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set myRng = sh1.Range("A1:A" & lr1) lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub What do I have to do to get this working? Thanks so much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Roger Govier" wrote: Hi Not sure I understand exactly what you are trying to do, but I think you have data in column A of sheet1, and want to look up those values from the first column of Sheet2 and return the value that exists in column B for Sheet2. If so then you need something like this for your ranges and Vlookups You will need to dim myrng2 as Range Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng2 to the used range in columns ' A and B on sheet2. lr2 count of column A Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng to the used range in column ' A on sheet1, lr1 being change to a count of ' Column A Set myRng = sh1.Range("A1:A" & _ lr1) 'set lr1 back to 1 to start on first row of sheet1 lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c The Countif function is there to test whether the value to be looked up exists in the rnage first, otherwise you will get an error 1004 if it does not exist. I'm not sure about setting lr1 as the count of rows in column B of sheet1 as you had it. If you run the code a second time, the results would be placed in rows below where the results occurred the first time. I think lr1 needs to set to 1 before you enter the loop, but I may have misinterpreted what you are trying to do. -- Regards Roger Govier "ryguy7272" wrote in message ... I made a few changes and actually made some progress on this, but now Im stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? Thanks!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: This is a bit confusing, but I think this is pretty close: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
There seems to be a limit at about 10,000 rows or so. After that I get a
message that reads 'Excel cannot complete this task with available resources. Choose less data or close other applications.' When I click Debug, the following line is yellow: ..Formula = "=VLOOKUP(A1,'" & strPath & "[" & strFile & "]Sheet1 '!A:C,2,0)&VLOOKUP(A1,'" & strPath & "[" & strFile & "]Sheet1'!A:C,3,0)" Is there an easy workaround for this, or do I need to try something else, like maybe Access? I'm totally comfortable working with Access, but I though it would be easier to do this in Excel. It's turning out to be not so easy, actually. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Ummmm, one more thing, Jacob. I put some test data in A & B & C, down to row 65536 (just on excel 2003 now but will upgrade to 2007 very soon). I get an error on this line: Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row) The error reads: Run-time error 1004 Method Range of object _Global failed. I tried a couple things, including naming the range, and then I tried this: Set rngTemp = Range(FullRange) Still getting the same error. Ultimately, this project may require 300,000 rows Ugh! What now? -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: You are most welcome and thanks for the feedback. "ryguy7272" wrote: Perfect!!! I was like, where did 'Jackpot' come from? Now it makes sense. Jacob, thanks for this, and all the other things too!! I appreciate it SOOOOO much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: Try below...(Modify to suit ) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:C,2,0)&VLOOKUP(A1,'" & _ strPath & "[" & strFile & "]Sheet1'!A:C,3,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With "ryguy7272" wrote: One more thing...I haven't see the data table yet, but I heard that ColB and ColC will have data, and I need to do a vlookup on BOTH of these columns, and pull the matching values into ColB of the Active Workbook. I tried an If...Then and I tried a couple different loops but couldn't get the desired results. How can I look for matches in either ColB or ColC, and pull the results back into the ColB of the Active Workbook? I hope that's doable. Thanks again!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks so much Jackpot! That is really slick and it works perfect!! Just before I read your post, I was actually just toggling back and forth b/w the Locals Window and the Immediate Window, trying to figure out why Roger's code wasn't working for me. Do you have any idea why that code would not work? This is a HUGE help! Thanks again! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jackpot" wrote: Hi Ryan You dont need to open the workbook or loop.. Try the below macro..(which I have tried.) Sub Macro() Dim rngTemp As Range, strPath As String, strFile As String strPath = "I:\Ryan\" strFile = "Book20.xls" Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:B,2,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With End Sub "ryguy7272" wrote: Sorry for the delay here. I had to take the CFA this past weekend and was preoccupied with that, these past few days. Finally, I can revisit this project. Yes, Roger, you are exactly right! I have data in Column A of Sheet1 in the active workbook, and want to look up matches from Column A in another workbook name Test2, and when there is a match, return the value that exists in Column B. I tested your code; it looks good, it doesnt actually do anything for me. There is no error; the Test2 opens and then closes, but nothing was updated. Did I do something wrong, perhaps? I have a named range myrng2, in Test2 (from A1:B7). The code, as it is now, is below. Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myrng2 As Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "C:\Users\New User\Desktop\Briefcase\Test2.xlsm" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("A1:B10") Set sh2 = Sheets("Sheet1") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set myRng = sh1.Range("A1:A" & lr1) lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub What do I have to do to get this working? Thanks so much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Roger Govier" wrote: Hi Not sure I understand exactly what you are trying to do, but I think you have data in column A of sheet1, and want to look up those values from the first column of Sheet2 and return the value that exists in column B for Sheet2. If so then you need something like this for your ranges and Vlookups You will need to dim myrng2 as Range Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng2 to the used range in columns ' A and B on sheet2. lr2 count of column A Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng to the used range in column ' A on sheet1, lr1 being change to a count of ' Column A Set myRng = sh1.Range("A1:A" & _ lr1) 'set lr1 back to 1 to start on first row of sheet1 lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c The Countif function is there to test whether the value to be looked up exists in the rnage first, otherwise you will get an error 1004 if it does not exist. I'm not sure about setting lr1 as the count of rows in column B of sheet1 as you had it. If you run the code a second time, the results would be placed in rows below where the results occurred the first time. I think lr1 needs to set to 1 before you enter the loop, but I may have misinterpreted what you are trying to do. -- Regards Roger Govier "ryguy7272" wrote in message ... I made a few changes and actually made some progress on this, but now Im stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and Copy/Paste b/w Two Workbooks
Hi Jacob
An interesting approach to let the Vlookup run without any pre-testing, then Replace the #N/A's. Very neat! -- Regards Roger Govier "Jackpot" wrote in message ... Hi Ryan You dont need to open the workbook or loop.. Try the below macro..(which I have tried.) Sub Macro() Dim rngTemp As Range, strPath As String, strFile As String strPath = "I:\Ryan\" strFile = "Book20.xls" Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row) With rngTemp .Formula = "=VLOOKUP(A1,'" & strPath & _ "[" & strFile & "]Sheet1'!A:B,2,0)" .Value = .Value .Replace "#N/A", "", xlWhole End With End Sub "ryguy7272" wrote: Sorry for the delay here. I had to take the CFA this past weekend and was preoccupied with that, these past few days. Finally, I can revisit this project. Yes, Roger, you are exactly right! I have data in Column A of Sheet1 in the active workbook, and want to look up matches from Column A in another workbook name Test2, and when there is a match, return the value that exists in Column B. I tested your code; it looks good, it doesnt actually do anything for me. There is no error; the Test2 opens and then closes, but nothing was updated. Did I do something wrong, perhaps? I have a named range myrng2, in Test2 (from A1:B7). The code, as it is now, is below. Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myrng2 As Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "C:\Users\New User\Desktop\Briefcase\Test2.xlsm" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("A1:B10") Set sh2 = Sheets("Sheet1") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set myRng = sh1.Range("A1:A" & lr1) lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub What do I have to do to get this working? Thanks so much!! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Roger Govier" wrote: Hi Not sure I understand exactly what you are trying to do, but I think you have data in column A of sheet1, and want to look up those values from the first column of Sheet2 and return the value that exists in column B for Sheet2. If so then you need something like this for your ranges and Vlookups You will need to dim myrng2 as Range Set sh2 = Sheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng2 to the used range in columns ' A and B on sheet2. lr2 count of column A Set myrng2 = sh2.Range("A1:B" & lr2) Set sh1 = Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' this sets myrng to the used range in column ' A on sheet1, lr1 being change to a count of ' Column A Set myRng = sh1.Range("A1:A" & _ lr1) 'set lr1 back to 1 to start on first row of sheet1 lr1 = 1 For Each c In myRng If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then sh1.Range("B" & lr1) = Application.WorksheetFunction. _ VLookup(c.Value, myrng2, 2, True) End If lr1 = lr1 + 1 Next c The Countif function is there to test whether the value to be looked up exists in the rnage first, otherwise you will get an error 1004 if it does not exist. I'm not sure about setting lr1 as the count of rows in column B of sheet1 as you had it. If you run the code a second time, the results would be placed in rows below where the results occurred the first time. I think lr1 needs to set to 1 before you enter the loop, but I may have misinterpreted what you are trying to do. -- Regards Roger Govier "ryguy7272" wrote in message ... I made a few changes and actually made some progress on this, but now Im stuck again. Heres my current code: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") Set sh2 = xlBook.Worksheets("Sheet2") lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set sh1 = ActiveWorkbook.Sheets("Sheet1") lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub As I F8 through the code, I can loop through one time, but the Excel puts a 1 in Cell B1 of Sheet Sheet1. This is NOT correct because there is no value in A1 of Sheet1 that matches A1 of Sheet2 (in the other Workbook). Also, on the second loop through, the code fails on this line: sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False) Error mssg reads: Run-time error 1004 Unable to get the Vlookup property of the WorksheetFunction class I did some googling for a solution but havent come up with anything obvious. What am I doing wrong with this WorksheetFunction.VLookup? Thanks!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: This is a bit confusing, but I think this is pretty close: Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim lr1 As Long Dim lr2 As Long Dim sh1 As Worksheet Dim c As Variant strFileName = "I:\Ryan\Book20.xls" Set xlApp = New Excel.Application xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10") lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" & lr2).Cells sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Book20").Sheets("Sheet2").Range("A1:B50 "), 2, False) lr1 = lr1 + 1 Next c If IsError(res) Then Else End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing End Sub An error occurs he lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count, 2).End(xlUp).Row Error mssg is 'Run-time error 9: subscript out of range' I guess the reference is not fully qualified, but it seems right to me . . . but something is still wrong. During my research of this, I found out that when you use vlookup in VBA, you can't access a closed workbook. So, I'm forcing that WB to open, then do the lookup, then close ONLY that 'Book20' NOT the WB that I'm running the code from. Can someone please get me back on track with this. Thanks so much!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks Jacob. I think I'm getting kind of close now. This is what I've got so far: Sheet1: CUSIP a w 111123 111124 111125 111126 Sheet2: t 1 g 1 h 1 y 1 111123 12 111124 13 111125 14 111126 15 w 1 I want to pull in the 12, 13, 14, and 15, into the appropriate row on Sheet1. Sub CopyOver() 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 lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row __________ Information from ESET Smart Security, version of virus signature database 5180 (20100607) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 5180 (20100607) __________ The message was checked by ESET Smart Security. http://www.eset.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Compare and copy/paste | Excel Worksheet Functions | |||
Macro to compare, find match and copy between workbooks | Excel Programming | |||
Compare two workbooks and Copy missing data | Excel Programming | |||
compare two ranges in different workbooks and copy data to a new workbook | Excel Programming |