I have this macro that compares values in two columns (A & M) in the same
sheet and if there is a match, it takes the value in the adjacent column (N)
and pastes it into ColumnE.

Sub MatchAandM()
Dim Lrow As Long
Dim RowCount As Long
Dim xRng As Range
Lrow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To Lrow
FindVal = Range("A" & RowCount)
Set xRng = Columns("M:M").Find(What:=FindVal, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not xRng Is Nothing Then
xRng.Offset(0, 1).Copy xRng.Offset(0, -8)
End If
Next RowCount
End Sub

Works great!! Now, what Im trying to do match numbers in Column E in Sheet
€œSheet1€� ActiveWorkbook with numbers in ColumnA of WorkBook named
€œMarketPrices€� and Sheet named €œMarketPrices€�, and if there is a match, copy
paste the value from ColumnB of €œMarketPrices€� to ColumnE of the €œSheet1€�
ActiveWorkbook. Make sense? To make this just a tad harder, both files are
stored on SharePoint!!

Heres my (non-working) code for moving the data between the two WorkBooks:
Sub MoveData()
Dim SSh As Worksheet 'source sheet
Dim DSh As Worksheet 'target sheet
Dim LastRow As Long
Dim CopyRange As String
Set DSh = ActiveWorkbook.ActiveSheet
'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary")
Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" &
Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) &
"MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")"
LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row
SSh.Range("B2:B" & LastRow).Copy
LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row
DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues)
End Sub

The code fails on this line:
Set SSh =

Run Time error 424
Object required.

I guess VBA is not recognizing the workbook, or sheet, or even range. I
cant figure out the problem with the object not found. Once that is
resolved, I need to use the basic logic form the first macro and incorporate
it into the second macro.

Any ideas?

Thanks so much!
Ryan---


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

Actually, I stand corrected. As I look at the first Sub, I now see that it
pulls the matched-value over and places it in Column E on the same row. What
I want is the VBA equivalent of Vlookup, or Index/Match. So the value to the
right of the matched value will not necessarily be copied/pasted to the same
row; if it was the same row it would be pure coincidence. The source and
destination will be different workbooks.

Thanks!


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


"ryguy7272" wrote:

I have this macro that compares values in two columns (A & M) in the same
sheet and if there is a match, it takes the value in the adjacent column (N)
and pastes it into ColumnE.

Sub MatchAandM()
Dim Lrow As Long
Dim RowCount As Long
Dim xRng As Range
Lrow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To Lrow
FindVal = Range("A" & RowCount)
Set xRng = Columns("M:M").Find(What:=FindVal, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not xRng Is Nothing Then
xRng.Offset(0, 1).Copy xRng.Offset(0, -8)
End If
Next RowCount
End Sub

Works great!! Now, what Im trying to do match numbers in Column E in Sheet
€œSheet1€� ActiveWorkbook with numbers in ColumnA of WorkBook named
€œMarketPrices€� and Sheet named €œMarketPrices€�, and if there is a match, copy
paste the value from ColumnB of €œMarketPrices€� to ColumnE of the €œSheet1€�
ActiveWorkbook. Make sense? To make this just a tad harder, both files are
stored on SharePoint!!

Heres my (non-working) code for moving the data between the two WorkBooks:
Sub MoveData()
Dim SSh As Worksheet 'source sheet
Dim DSh As Worksheet 'target sheet
Dim LastRow As Long
Dim CopyRange As String
Set DSh = ActiveWorkbook.ActiveSheet
'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary")
Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" &
Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) &
"MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")"
LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row
SSh.Range("B2:B" & LastRow).Copy
LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row
DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues)
End Sub

The code fails on this line:
Set SSh =

Run Time error 424
Object required.

I guess VBA is not recognizing the workbook, or sheet, or even range. I
cant figure out the problem with the object not found. Once that is
resolved, I need to use the basic logic form the first macro and incorporate
it into the second macro.

Any ideas?

Thanks so much!
Ryan---


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

Try the below in VBA...

Worksheetfunction.Vlookup()

--
Jacob (MVP - Excel)


"ryguy7272" wrote:

Actually, I stand corrected. As I look at the first Sub, I now see that it
pulls the matched-value over and places it in Column E on the same row. What
I want is the VBA equivalent of Vlookup, or Index/Match. So the value to the
right of the matched value will not necessarily be copied/pasted to the same
row; if it was the same row it would be pure coincidence. The source and
destination will be different workbooks.

Thanks!


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


"ryguy7272" wrote:

I have this macro that compares values in two columns (A & M) in the same
sheet and if there is a match, it takes the value in the adjacent column (N)
and pastes it into ColumnE.

Sub MatchAandM()
Dim Lrow As Long
Dim RowCount As Long
Dim xRng As Range
Lrow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To Lrow
FindVal = Range("A" & RowCount)
Set xRng = Columns("M:M").Find(What:=FindVal, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not xRng Is Nothing Then
xRng.Offset(0, 1).Copy xRng.Offset(0, -8)
End If
Next RowCount
End Sub

Works great!! Now, what Im trying to do match numbers in Column E in Sheet
€œSheet1€� ActiveWorkbook with numbers in ColumnA of WorkBook named
€œMarketPrices€� and Sheet named €œMarketPrices€�, and if there is a match, copy
paste the value from ColumnB of €œMarketPrices€� to ColumnE of the €œSheet1€�
ActiveWorkbook. Make sense? To make this just a tad harder, both files are
stored on SharePoint!!

Heres my (non-working) code for moving the data between the two WorkBooks:
Sub MoveData()
Dim SSh As Worksheet 'source sheet
Dim DSh As Worksheet 'target sheet
Dim LastRow As Long
Dim CopyRange As String
Set DSh = ActiveWorkbook.ActiveSheet
'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary")
Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" &
Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) &
"MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")"
LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row
SSh.Range("B2:B" & LastRow).Copy
LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row
DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues)
End Sub

The code fails on this line:
Set SSh =

Run Time error 424
Object required.

I guess VBA is not recognizing the workbook, or sheet, or even range. I
cant figure out the problem with the object not found. Once that is
resolved, I need to use the basic logic form the first macro and incorporate
it into the second macro.

Any ideas?

Thanks so much!
Ryan---


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

Thanks Jacob. I think I'm getting kind of close now. This is what I've got
so far:
Sheet1:
CUSIP
a
w
111123
111124
111125
111126

Sheet2:
t 1
g 1
h 1
y 1
111123 12
111124 13
111125 14
111126 15
w 1

I want to pull in the 12, 13, 14, and 15, into the appropriate row on
Sheet1.

Sub CopyOver()
Dim lr1 As Long, lr2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Sheet1")
Set sh2 = ActiveWorkbook.Sheets("Sheet2")

lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row
lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row

For Each c In Worksheets("Sheet2").Range("A1:C" & lr2).Cells
sh1.Range("B" & lr1 + 1) = Application.WorksheetFunction.VLookup(c,
MktPrice, 2, False)
Next c

End Sub

Right now I get an error on this line:
sh1.Range("B" & lr1 + 1) . . .

Error mssg is: run-time error '1004'
Unable to get the Vlookup property of the WorksheetFunction class

MktPrice is a NamedRange, but I would ultimately like to identify a used
range, similar to this:

..Range("A1:C" & lr2).Cells

Finally, ultimately sh1 and sh2 will be in two different workbooks. I've
seen a few examples of how to do that online, but not sure how to set it up.
I figured I'd try to get the 2-sheets-in-the-same-workbook concept working
first . . . If someone can help me get this setup for two different
workbooks, Id love to see that now!

Finally, does the Worksheetfunction.Vlookup() have the same requirements as
the =vlookup() function? Specifically, table_array is two or more columns of
data that is sorted in ascending order. Im not 100% sure this will always
be the way the data comes through. I would prefer to use Index/Match in VBA,
if there is such a thing, or if Worksheetfunction.Vlookup() doesnt have the
€˜data is sorted in ascending order limitation.

Thanks for everything!!


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


"Jacob Skaria" wrote:

Try the below in VBA...

Worksheetfunction.Vlookup()

--
Jacob (MVP - Excel)


"ryguy7272" wrote:

Actually, I stand corrected. As I look at the first Sub, I now see that it
pulls the matched-value over and places it in Column E on the same row. What
I want is the VBA equivalent of Vlookup, or Index/Match. So the value to the
right of the matched value will not necessarily be copied/pasted to the same
row; if it was the same row it would be pure coincidence. The source and
destination will be different workbooks.

Thanks!


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


"ryguy7272" wrote:

I have this macro that compares values in two columns (A & M) in the same
sheet and if there is a match, it takes the value in the adjacent column (N)
and pastes it into ColumnE.

Sub MatchAandM()
Dim Lrow As Long
Dim RowCount As Long
Dim xRng As Range
Lrow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To Lrow
FindVal = Range("A" & RowCount)
Set xRng = Columns("M:M").Find(What:=FindVal, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not xRng Is Nothing Then
xRng.Offset(0, 1).Copy xRng.Offset(0, -8)
End If
Next RowCount
End Sub

Works great!! Now, what Im trying to do match numbers in Column E in Sheet
€œSheet1€� ActiveWorkbook with numbers in ColumnA of WorkBook named
€œMarketPrices€� and Sheet named €œMarketPrices€�, and if there is a match, copy
paste the value from ColumnB of €œMarketPrices€� to ColumnE of the €œSheet1€�
ActiveWorkbook. Make sense? To make this just a tad harder, both files are
stored on SharePoint!!

Heres my (non-working) code for moving the data between the two WorkBooks:
Sub MoveData()
Dim SSh As Worksheet 'source sheet
Dim DSh As Worksheet 'target sheet
Dim LastRow As Long
Dim CopyRange As String
Set DSh = ActiveWorkbook.ActiveSheet
'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary")
Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" &
Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) &
"MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")"
LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row
SSh.Range("B2:B" & LastRow).Copy
LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row
DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues)
End Sub

The code fails on this line:
Set SSh =

Run Time error 424
Object required.

I guess VBA is not recognizing the workbook, or sheet, or even range. I
cant figure out the problem with the object not found. Once that is
resolved, I need to use the basic logic form the first macro and incorporate
it into the second macro.

Any ideas?

Thanks so much!
Ryan---


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

This is a bit confusing, but I think this is pretty close:
Sub testme()

Dim xlApp As Excel.Application
Dim xlBook As New Excel.Workbook
Dim strFileName As String
Dim res As Variant
Dim myRng As Excel.Range
Dim lr1 As Long
Dim lr2 As Long
Dim sh1 As Worksheet
Dim c As Variant

strFileName = "I:\Ryan\Book20.xls"

Set xlApp = New Excel.Application
xlApp.Visible = True

Set xlBook = xlApp.Workbooks.Open(strFileName, True)
Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10")

lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count,
2).End(xlUp).Row
lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row

For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" &
lr2).Cells
sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value,
Workbooks("Book20").Sheets("Sheet2").Range("A1:B50 "), 2, False)
lr1 = lr1 + 1
Next c

If IsError(res) Then

Else

End If

xlBook.Close savechanges:=False
xlApp.Quit

Set myRng = Nothing

End Sub

An error occurs he
lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count,
2).End(xlUp).Row

Error mssg is 'Run-time error 9: subscript out of range'
I guess the reference is not fully qualified, but it seems right to me . . .
but something is still wrong.

During my research of this, I found out that when you use vlookup in VBA,
you can't access a closed workbook. So, I'm forcing that WB to open, then do
the lookup, then close ONLY that 'Book20' NOT the WB that I'm running the
code from. Can someone please get me back on track with this.

Thanks so much!!


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


"ryguy7272" wrote:

Thanks Jacob. I think I'm getting kind of close now. This is what I've got
so far:
Sheet1:
CUSIP
a
w
111123
111124
111125
111126

Sheet2:
t 1
g 1
h 1
y 1
111123 12
111124 13
111125 14
111126 15
w 1

I want to pull in the 12, 13, 14, and 15, into the appropriate row on
Sheet1.

Sub CopyOver()
Dim lr1 As Long, lr2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Sheet1")
Set sh2 = ActiveWorkbook.Sheets("Sheet2")

lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row
lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row

For Each c In Worksheets("Sheet2").Range("A1:C" & lr2).Cells
sh1.Range("B" & lr1 + 1) = Application.WorksheetFunction.VLookup(c,
MktPrice, 2, False)
Next c

End Sub

Right now I get an error on this line:
sh1.Range("B" & lr1 + 1) . . .

Error mssg is: run-time error '1004'
Unable to get the Vlookup property of the WorksheetFunction class

MktPrice is a NamedRange, but I would ultimately like to identify a used
range, similar to this:

.Range("A1:C" & lr2).Cells

Finally, ultimately sh1 and sh2 will be in two different workbooks. I've
seen a few examples of how to do that online, but not sure how to set it up.
I figured I'd try to get the 2-sheets-in-the-same-workbook concept working
first . . . If someone can help me get this setup for two different
workbooks, Id love to see that now!

Finally, does the Worksheetfunction.Vlookup() have the same requirements as
the =vlookup() function? Specifically, table_array is two or more columns of
data that is sorted in ascending order. Im not 100% sure this will always
be the way the data comes through. I would prefer to use Index/Match in VBA,
if there is such a thing, or if Worksheetfunction.Vlookup() doesnt have the
€˜data is sorted in ascending order limitation.

Thanks for everything!!


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


"Jacob Skaria" wrote:

Try the below in VBA...

Worksheetfunction.Vlookup()

--
Jacob (MVP - Excel)


"ryguy7272" wrote:

Actually, I stand corrected. As I look at the first Sub, I now see that it
pulls the matched-value over and places it in Column E on the same row. What
I want is the VBA equivalent of Vlookup, or Index/Match. So the value to the
right of the matched value will not necessarily be copied/pasted to the same
row; if it was the same row it would be pure coincidence. The source and
destination will be different workbooks.

Thanks!


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


"ryguy7272" wrote:

I have this macro that compares values in two columns (A & M) in the same
sheet and if there is a match, it takes the value in the adjacent column (N)
and pastes it into ColumnE.

Sub MatchAandM()
Dim Lrow As Long
Dim RowCount As Long
Dim xRng As Range
Lrow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To Lrow
FindVal = Range("A" & RowCount)
Set xRng = Columns("M:M").Find(What:=FindVal, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not xRng Is Nothing Then
xRng.Offset(0, 1).Copy xRng.Offset(0, -8)
End If
Next RowCount
End Sub

Works great!! Now, what Im trying to do match numbers in Column E in Sheet
€œSheet1€� ActiveWorkbook with numbers in ColumnA of WorkBook named
€œMarketPrices€� and Sheet named €œMarketPrices€�, and if there is a match, copy
paste the value from ColumnB of €œMarketPrices€� to ColumnE of the €œSheet1€�
ActiveWorkbook. Make sense? To make this just a tad harder, both files are
stored on SharePoint!!

Heres my (non-working) code for moving the data between the two WorkBooks:
Sub MoveData()
Dim SSh As Worksheet 'source sheet
Dim DSh As Worksheet 'target sheet
Dim LastRow As Long
Dim CopyRange As String
Set DSh = ActiveWorkbook.ActiveSheet
'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary")
Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" &
Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) &
"MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")"
LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row
SSh.Range("B2:B" & LastRow).Copy
LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row
DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues)
End Sub

The code fails on this line:
Set SSh =

Run Time error 424
Object required.

I guess VBA is not recognizing the workbook, or sheet, or even range. I
cant figure out the problem with the object not found. Once that is
resolved, I need to use the basic logic form the first macro and incorporate
it into the second macro.

Any ideas?

Thanks so much!
Ryan---


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

I made a few changes and actually made some progress on this, but now Im
stuck again. Heres my current code:

Sub testme()

Dim xlApp As Excel.Application
Dim xlBook As New Excel.Workbook
Dim strFileName As String
Dim res As Variant
Dim myRng As Excel.Range
Dim lr1 As Long
Dim lr2 As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim c As Variant

strFileName = "I:\Ryan\Book20.xls"

Set xlApp = New Excel.Application
xlApp.Visible = True

Set xlBook = xlApp.Workbooks.Open(strFileName, True)
Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10")

Set sh2 = xlBook.Worksheets("Sheet2")
lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row

Set sh1 = ActiveWorkbook.Sheets("Sheet1")
lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row

For Each c In xlBook.Worksheets("Sheet2").Range("A1:B" & lr2).Cells
sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value,
xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False)
lr1 = lr1 + 1
Next c

If IsError(res) Then

Else

End If

xlBook.Close savechanges:=False
xlApp.Quit

Set myRng = Nothing

End Sub

As I F8 through the code, I can loop through one time, but the Excel puts a
€˜1 in Cell B1 of Sheet €˜Sheet1. This is NOT correct because there is no
value in A1 of €˜Sheet1 that matches A1 of €˜Sheet2 (in the other Workbook).
Also, on the second loop through, the code fails on this line:

sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value,
xlBook.Worksheets("Sheet2").Range("A1:B50"), 2, False)

Error mssg reads: €˜Run-time error 1004 Unable to get the Vlookup property of
the WorksheetFunction class

I did some googling for a solution but havent come up with anything
obvious. What am I doing wrong with this WorksheetFunction.VLookup?

Thanks!!


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


"ryguy7272" wrote:

This is a bit confusing, but I think this is pretty close:
Sub testme()

Dim xlApp As Excel.Application
Dim xlBook As New Excel.Workbook
Dim strFileName As String
Dim res As Variant
Dim myRng As Excel.Range
Dim lr1 As Long
Dim lr2 As Long
Dim sh1 As Worksheet
Dim c As Variant

strFileName = "I:\Ryan\Book20.xls"

Set xlApp = New Excel.Application
xlApp.Visible = True

Set xlBook = xlApp.Workbooks.Open(strFileName, True)
Set myRng = xlBook.Worksheets("Sheet2").Range("A1:B10")

lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count,
2).End(xlUp).Row
lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row

For Each c In Workbooks("Book20.xls").Sheets("Sheet2").Range("A1 :B" &
lr2).Cells
sh1.Range("B" & lr1) = Application.WorksheetFunction.VLookup(c.Value,
Workbooks("Book20").Sheets("Sheet2").Range("A1:B50 "), 2, False)
lr1 = lr1 + 1
Next c

If IsError(res) Then

Else

End If

xlBook.Close savechanges:=False
xlApp.Quit

Set myRng = Nothing

End Sub

An error occurs he
lr2 = Workbooks("Book20.xls").Sheets("Sheet2").Cells(Row s.Count,
2).End(xlUp).Row

Error mssg is 'Run-time error 9: subscript out of range'
I guess the reference is not fully qualified, but it seems right to me . . .
but something is still wrong.

During my research of this, I found out that when you use vlookup in VBA,
you can't access a closed workbook. So, I'm forcing that WB to open, then do
the lookup, then close ONLY that 'Book20' NOT the WB that I'm running the
code from. Can someone please get me back on track with this.

Thanks so much!!


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


"ryguy7272" wrote:

Thanks Jacob. I think I'm getting kind of close now. This is what I've got
so far:
Sheet1:
CUSIP
a
w
111123
111124
111125
111126

Sheet2:
t 1
g 1
h 1
y 1
111123 12
111124 13
111125 14
111126 15
w 1

I want to pull in the 12, 13, 14, and 15, into the appropriate row on
Sheet1.

Sub CopyOver()
Dim lr1 As Long, lr2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Sheet1")
Set sh2 = ActiveWorkbook.Sheets("Sheet2")

lr2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row
lr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row

For Each c In Worksheets("Sheet2").Range("A1:C" & lr2).Cells
sh1.Range("B" & lr1 + 1) = Application.WorksheetFunction.VLookup(c,
MktPrice, 2, False)
Next c

End Sub

Right now I get an error on this line:
sh1.Range("B" & lr1 + 1) . . .

Error mssg is: run-time error '1004'
Unable to get the Vlookup property of the WorksheetFunction class

MktPrice is a NamedRange, but I would ultimately like to identify a used
range, similar to this:

.Range("A1:C" & lr2).Cells

Finally, ultimately sh1 and sh2 will be in two different workbooks. I've
seen a few examples of how to do that online, but not sure how to set it up.
I figured I'd try to get the 2-sheets-in-the-same-workbook concept working
first . . . If someone can help me get this setup for two different
workbooks, Id love to see that now!

Finally, does the Worksheetfunction.Vlookup() have the same requirements as
the =vlookup() function? Specifically, table_array is two or more columns of
data that is sorted in ascending order. Im not 100% sure this will always
be the way the data comes through. I would prefer to use Index/Match in VBA,
if there is such a thing, or if Worksheetfunction.Vlookup() doesnt have the
€˜data is sorted in ascending order limitation.

Thanks for everything!!


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


"Jacob Skaria" wrote:

Try the below in VBA...

Worksheetfunction.Vlookup()

--
Jacob (MVP - Excel)


"ryguy7272" wrote:

Actually, I stand corrected. As I look at the first Sub, I now see that it
pulls the matched-value over and places it in Column E on the same row. What
I want is the VBA equivalent of Vlookup, or Index/Match. So the value to the
right of the matched value will not necessarily be copied/pasted to the same
row; if it was the same row it would be pure coincidence. The source and
destination will be different workbooks.

Thanks!


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


"ryguy7272" wrote:

I have this macro that compares values in two columns (A & M) in the same
sheet and if there is a match, it takes the value in the adjacent column (N)
and pastes it into ColumnE.

Sub MatchAandM()
Dim Lrow As Long
Dim RowCount As Long
Dim xRng As Range
Lrow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To Lrow
FindVal = Range("A" & RowCount)
Set xRng = Columns("M:M").Find(What:=FindVal, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not xRng Is Nothing Then
xRng.Offset(0, 1).Copy xRng.Offset(0, -8)
End If
Next RowCount
End Sub

Works great!! Now, what Im trying to do match numbers in Column E in Sheet
€œSheet1€� ActiveWorkbook with numbers in ColumnA of WorkBook named
€œMarketPrices€� and Sheet named €œMarketPrices€�, and if there is a match, copy
paste the value from ColumnB of €œMarketPrices€� to ColumnE of the €œSheet1€�
ActiveWorkbook. Make sense? To make this just a tad harder, both files are
stored on SharePoint!!

Heres my (non-working) code for moving the data between the two WorkBooks:
Sub MoveData()
Dim SSh As Worksheet 'source sheet
Dim DSh As Worksheet 'target sheet
Dim LastRow As Long
Dim CopyRange As String
Set DSh = ActiveWorkbook.ActiveSheet
'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary")
Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" &
Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) &
"MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")"
LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row
SSh.Range("B2:B" & LastRow).Copy
LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row
DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues)
End Sub

The code fails on this line:
Set SSh =

Run Time error 424
Object required.

I guess VBA is not recognizing the workbook, or sheet, or even range. I
cant figure out the problem with the object not found. Once that is
resolved, I need to use the basic logic form the first macro and incorporate
it into the second macro.

Any ideas?

Thanks so much!
Ryan---


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