Compare and Copy/Paste b/w Two Workbooks

R

ryguy7272

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 I’m 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!!

Here’s 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 Documents/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
can’t 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---
 
R

ryguy7272

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!
 
R

ryguy7272

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, I’d 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. I’m 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() doesn’t have the
‘data is sorted in ascending order’ limitation.

Thanks for everything!!
 
R

ryguy7272

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(Rows.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 here:
lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Rows.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!!
 
R

ryguy7272

I made a few changes and actually made some progress on this, but now I’m
stuck again. Here’s 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 haven’t come up with anything
obvious. What am I doing wrong with this WorksheetFunction.VLookup?

Thanks!!
 
R

Roger Govier

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 said:
I made a few changes and actually made some progress on this, but now I’m
stuck again. Here’s 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 haven’t 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''.




__________ Information from ESET Smart Security, version of virus
signature database 5173 (20100604) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 5173 (20100604) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
R

ryguy7272

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 doesn’t
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--
 
J

Jackpot

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
 
R

ryguy7272

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 said:
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 said:
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 doesn’t
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--
Click to expand...
Click to expand...
 
J

Jackpot

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 said:
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 said:
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 said:
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 doesn’t
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''.


:

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

I made a few changes and actually made some progress on this, but now I’m
stuck again. Here’s 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 haven’t 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''.


:

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(Rows.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 here:
lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Rows.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.
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

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 said:
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 said:
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 said:
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 doesn’t
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''.


:

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

I made a few changes and actually made some progress on this, but now I’m
stuck again. Here’s 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 haven’t 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''.


:

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(Rows.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 here:
lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Rows.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.
Click to expand...
Click to expand...
Click to expand...
 
J

Jackpot

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 said:
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 said:
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 said:
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




:

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 doesn’t
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''.


:

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

I made a few changes and actually made some progress on this, but now I’m
stuck again. Here’s 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 haven’t 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''.


:

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(Rows.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
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

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 said:
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 said:
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 said:
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''.


:

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




:

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 doesn’t
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''.


:

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

I made a few changes and actually made some progress on this, but now I’m
stuck again. Here’s 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 haven’t 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''.


:

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(Rows.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
Click to expand...
Click to expand...
Click to expand...
 
J

Jackpot

You are most welcome and thanks for the feedback.

ryguy7272 said:
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 said:
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 said:
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''.


:

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


:

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




:

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 doesn’t
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''.


:

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

I made a few changes and actually made some progress on this, but now I’m
stuck again. Here’s 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 haven’t 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''.


:

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"
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

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 said:
You are most welcome and thanks for the feedback.

ryguy7272 said:
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 said:
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


:

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


:

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


:

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




:

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 doesn’t
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''.


:

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

I made a few changes and actually made some progress on this, but now I’m
stuck again. Here’s 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 haven’t 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''.


:

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
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

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 said:
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 said:
You are most welcome and thanks for the feedback.

ryguy7272 said:
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''.


:

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


:

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


:

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


:

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




:

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 doesn’t
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''.


:

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

I made a few changes and actually made some progress on this, but now I’m
stuck again. Here’s 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 haven’t come up with anything
obvious. What am I doing wrong with this WorksheetFunction.VLookup?
Click to expand...
Click to expand...
Click to expand...
 
R

Roger Govier

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 said:
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 said:
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 doesn’t
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--
Click to expand...

__________ Information from ESET Smart Security, version of virus
signature database 5180 (20100607) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 5180 (20100607) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy varaible no. of rows to another woorkbook 2
Compare Col A and Col M, if Match, Copy Col N to Col E 6
Copy from one Sheet and Paste to Another 4
Search CSV for string. 5
copy and paste a row 16
Problem with code - Help needed please 3
Macro copy values to a different workbook 4
need rons help on email.(recall temp file?) 3

Top