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.
|