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


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

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 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%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
> 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---
>
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.

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 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%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
> > 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---
> >
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.

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


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

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, 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!!
>
>
> --
> 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 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%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
> > > > 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---
> > > >
> > > >
> > > > --
> > > > Ryan---
> > > > If this information was helpful, please indicate this by clicking ''Yes''.

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


"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(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!!
>
>
> --
> 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, 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!!
> >
> >
> > --
> > 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 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%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
> > > > > 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---
> > > > >
> > > > >
> > > > > --
> > > > > 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

"ryguy7272" <(E-Mail Removed)> wrote in message
newsB565265-3A8C-4938-BB02-(E-Mail Removed)...
> 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''.
>
>
> "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(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!!
>>
>>
>> --
>> 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, 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!!
>> >
>> >
>> > --
>> > 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 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%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
>> > > > > 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---
>> > > > >
>> > > > >
>> > > > > --
>> > > > > 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

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


"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" <(E-Mail Removed)> wrote in message
> newsB565265-3A8C-4938-BB02-(E-Mail Removed)...
> > 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''.
> >
> >
> > "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(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!!
> >>
> >>
> >> --
> >> 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, 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!!
> >> >
> >> >
> >> > --
> >> > 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

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 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''.
>
>
> "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" <(E-Mail Removed)> wrote in message
> > newsB565265-3A8C-4938-BB02-(E-Mail Removed)...
> > > 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''.
> > >
> > >
> > > "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(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!!
> > >>
> > >>
> > >> --
> > >> 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

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 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''.
> >
> >
> > "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" <(E-Mail Removed)> wrote in message
> > > newsB565265-3A8C-4938-BB02-(E-Mail Removed)...
> > > > 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''.
> > > >
> > > >
> > > > "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(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!!
> > > >>
> > > >>
> > > >> --
> > > >> 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: