Compare and Copy/Paste b/w Two Workbooks

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

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

Works great!!  Now, what 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''.
0
Utf
6/3/2010 7:56:46 PM
excel.programming 6508 articles. 2 followers. Follow

17 Replies
1055 Views

Similar Articles

[PageSpeed] 21

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''.
0
Utf
6/3/2010 10:43:14 PM
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''.
0
Utf
6/4/2010 4:20:52 AM
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''.
0
Utf
6/4/2010 2:28:37 PM
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''.
0
Utf
6/4/2010 4:11:47 PM
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''.
0
Utf
6/4/2010 8:34:42 PM
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" <ryguy7272@discussions.microsoft.com> wrote in message 
news:DB565265-3A8C-4938-BB02-CE6E4FE7A108@microsoft.com...
> 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



0
Roger
6/5/2010 9:56:38 AM
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" <ryguy7272@discussions.microsoft.com> wrote in message 
> news:DB565265-3A8C-4938-BB02-CE6E4FE7A108@microsoft.com...
> > 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
0
Utf
6/7/2010 3:02:39 AM
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" <ryguy7272@discussions.microsoft.com> wrote in message 
> > news:DB565265-3A8C-4938-BB02-CE6E4FE7A108@microsoft.com...
> > > 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
0
Utf
6/7/2010 2:02:24 PM
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" <ryguy7272@discussions.microsoft.com> wrote in message 
> > > news:DB565265-3A8C-4938-BB02-CE6E4FE7A108@microsoft.com...
> > > > 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:
0
Utf
6/7/2010 2:31:49 PM
Hi Ryan

I am not sure why it does not work for you. I tried the same in a new 
workbook Sheet1 and Sheet2..with sample data you posted as a response to my 
initial post (display name has been changed from Jacob)...It works...

Sub Macro()

Set sh2 = Sheets("Sheet2")
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set myrng2 = sh2.Range("A1:B" & lr2)

Set sh1 = Sheets("Sheet1")
lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set myRng = sh1.Range("A1:A" & lr1)

lr1 = 1

For Each c In myRng
If Application.WorksheetFunction.CountIf(myrng2, c.Value) Then
sh1.Range("B" & lr1) = Application.WorksheetFunction. _
VLookup(c.Value, myrng2, 2, False)
End If
lr1 = lr1 + 1
Next c

End Sub



"ryguy7272" wrote:

> Thanks so much Jackpot!  That is really slick and it works perfect!!  Just 
> before I read your post, I was actually just toggling back and forth b/w the 
> Locals Window and the Immediate Window, trying to figure out why Roger's code 
> wasn't working for me.  Do you have any idea why that code would not work?  
> 
> This is a HUGE help!  Thanks again!
> Ryan--
> 
> -- 
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
> 
> 
> "Jackpot" wrote:
> 
> > Hi Ryan
> > 
> > You dont need to open the workbook or loop.. Try the below macro..(which I 
> > have tried.)
> > 
> > 
> > Sub Macro()
> > 
> > Dim rngTemp As Range, strPath As String, strFile As String
> > 
> > strPath = "I:\Ryan\"
> > strFile = "Book20.xls"
> > 
> > Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
> > 
> > With rngTemp
> >     .Formula = "=VLOOKUP(A1,'" & strPath & _
> >     "[" & strFile & "]Sheet1'!A:B,2,0)"
> >     .Value = .Value
> >     .Replace "#N/A", "", xlWhole
> > End With
> > 
> > 
> > End Sub
> > 
> > 
> > 
> > 
> > "ryguy7272" wrote:
> > 
> > > Sorry for the delay here.  I had to take the CFA this past weekend and was 
> > > preoccupied with that, these past few days.  Finally, I can revisit this 
> > > project.  Yes, Roger, you are exactly right!  I have data in Column A of 
> > > Sheet1 in the active workbook, and want to look up matches from Column A in 
> > > another workbook name ‘Test2’, and when there is a match, return the value 
> > > that exists in Column B.  I tested your code; it looks good, it 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" <ryguy7272@discussions.microsoft.com> wrote in message 
> > > > news:DB565265-3A8C-4938-BB02-CE6E4FE7A108@microsoft.com...
> > > > > 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.
> > > > >>
0
Utf
6/7/2010 2:54:03 PM
One more thing...I haven't see the data table yet, but I heard that ColB and 
ColC will have data, and I need to do a vlookup on BOTH of these columns, and 
pull the matching values into ColB of the Active Workbook.  

I tried an If...Then and I tried a couple different loops but couldn't get 
the desired results.  How can I look for matches in either ColB or ColC, and 
pull the results back into the ColB of the Active Workbook?

I hope that's doable.

Thanks again!!
Ryan--

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


"ryguy7272" wrote:

> Thanks so much Jackpot!  That is really slick and it works perfect!!  Just 
> before I read your post, I was actually just toggling back and forth b/w the 
> Locals Window and the Immediate Window, trying to figure out why Roger's code 
> wasn't working for me.  Do you have any idea why that code would not work?  
> 
> This is a HUGE help!  Thanks again!
> Ryan--
> 
> -- 
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
> 
> 
> "Jackpot" wrote:
> 
> > Hi Ryan
> > 
> > You dont need to open the workbook or loop.. Try the below macro..(which I 
> > have tried.)
> > 
> > 
> > Sub Macro()
> > 
> > Dim rngTemp As Range, strPath As String, strFile As String
> > 
> > strPath = "I:\Ryan\"
> > strFile = "Book20.xls"
> > 
> > Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
> > 
> > With rngTemp
> >     .Formula = "=VLOOKUP(A1,'" & strPath & _
> >     "[" & strFile & "]Sheet1'!A:B,2,0)"
> >     .Value = .Value
> >     .Replace "#N/A", "", xlWhole
> > End With
> > 
> > 
> > End Sub
> > 
> > 
> > 
> > 
> > "ryguy7272" wrote:
> > 
> > > Sorry for the delay here.  I had to take the CFA this past weekend and was 
> > > preoccupied with that, these past few days.  Finally, I can revisit this 
> > > project.  Yes, Roger, you are exactly right!  I have data in Column A of 
> > > Sheet1 in the active workbook, and want to look up matches from Column A in 
> > > another workbook name ‘Test2’, and when there is a match, return the value 
> > > that exists in Column B.  I tested your code; it looks good, it 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" <ryguy7272@discussions.microsoft.com> wrote in message 
> > > > news:DB565265-3A8C-4938-BB02-CE6E4FE7A108@microsoft.com...
> > > > > 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.
> > > > >>
0
Utf
6/7/2010 2:56:23 PM
Try below...(Modify to suit )

With rngTemp
    .Formula = "=VLOOKUP(A1,'" & strPath & _
    "[" & strFile & "]Sheet1'!A:C,2,0)&VLOOKUP(A1,'" & _
    strPath & "[" & strFile & "]Sheet1'!A:C,3,0)"
    .Value = .Value
    .Replace "#N/A", "", xlWhole
End With


"ryguy7272" wrote:

> One more thing...I haven't see the data table yet, but I heard that ColB and 
> ColC will have data, and I need to do a vlookup on BOTH of these columns, and 
> pull the matching values into ColB of the Active Workbook.  
> 
> I tried an If...Then and I tried a couple different loops but couldn't get 
> the desired results.  How can I look for matches in either ColB or ColC, and 
> pull the results back into the ColB of the Active Workbook?
> 
> I hope that's doable.
> 
> Thanks again!!
> Ryan--
> 
> -- 
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
> 
> 
> "ryguy7272" wrote:
> 
> > Thanks so much Jackpot!  That is really slick and it works perfect!!  Just 
> > before I read your post, I was actually just toggling back and forth b/w the 
> > Locals Window and the Immediate Window, trying to figure out why Roger's code 
> > wasn't working for me.  Do you have any idea why that code would not work?  
> > 
> > This is a HUGE help!  Thanks again!
> > Ryan--
> > 
> > -- 
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> > 
> > 
> > "Jackpot" wrote:
> > 
> > > Hi Ryan
> > > 
> > > You dont need to open the workbook or loop.. Try the below macro..(which I 
> > > have tried.)
> > > 
> > > 
> > > Sub Macro()
> > > 
> > > Dim rngTemp As Range, strPath As String, strFile As String
> > > 
> > > strPath = "I:\Ryan\"
> > > strFile = "Book20.xls"
> > > 
> > > Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
> > > 
> > > With rngTemp
> > >     .Formula = "=VLOOKUP(A1,'" & strPath & _
> > >     "[" & strFile & "]Sheet1'!A:B,2,0)"
> > >     .Value = .Value
> > >     .Replace "#N/A", "", xlWhole
> > > End With
> > > 
> > > 
> > > End Sub
> > > 
> > > 
> > > 
> > > 
> > > "ryguy7272" wrote:
> > > 
> > > > Sorry for the delay here.  I had to take the CFA this past weekend and was 
> > > > preoccupied with that, these past few days.  Finally, I can revisit this 
> > > > project.  Yes, Roger, you are exactly right!  I have data in Column A of 
> > > > Sheet1 in the active workbook, and want to look up matches from Column A in 
> > > > another workbook name ‘Test2’, and when there is a match, return the value 
> > > > that exists in Column B.  I tested your code; it looks good, it 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" <ryguy7272@discussions.microsoft.com> wrote in message 
> > > > > news:DB565265-3A8C-4938-BB02-CE6E4FE7A108@microsoft.com...
> > > > > > 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
0
Utf
6/7/2010 3:03:10 PM
Perfect!!!  I was like, where did 'Jackpot' come from?  Now it makes sense.  
Jacob, thanks for this, and all the other things too!!
I appreciate it SOOOOO much!!
Ryan--

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


"Jackpot" wrote:

> Try below...(Modify to suit )
> 
> With rngTemp
>     .Formula = "=VLOOKUP(A1,'" & strPath & _
>     "[" & strFile & "]Sheet1'!A:C,2,0)&VLOOKUP(A1,'" & _
>     strPath & "[" & strFile & "]Sheet1'!A:C,3,0)"
>     .Value = .Value
>     .Replace "#N/A", "", xlWhole
> End With
> 
> 
> "ryguy7272" wrote:
> 
> > One more thing...I haven't see the data table yet, but I heard that ColB and 
> > ColC will have data, and I need to do a vlookup on BOTH of these columns, and 
> > pull the matching values into ColB of the Active Workbook.  
> > 
> > I tried an If...Then and I tried a couple different loops but couldn't get 
> > the desired results.  How can I look for matches in either ColB or ColC, and 
> > pull the results back into the ColB of the Active Workbook?
> > 
> > I hope that's doable.
> > 
> > Thanks again!!
> > Ryan--
> > 
> > -- 
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> > 
> > 
> > "ryguy7272" wrote:
> > 
> > > Thanks so much Jackpot!  That is really slick and it works perfect!!  Just 
> > > before I read your post, I was actually just toggling back and forth b/w the 
> > > Locals Window and the Immediate Window, trying to figure out why Roger's code 
> > > wasn't working for me.  Do you have any idea why that code would not work?  
> > > 
> > > This is a HUGE help!  Thanks again!
> > > Ryan--
> > > 
> > > -- 
> > > Ryan---
> > > If this information was helpful, please indicate this by clicking ''Yes''.
> > > 
> > > 
> > > "Jackpot" wrote:
> > > 
> > > > Hi Ryan
> > > > 
> > > > You dont need to open the workbook or loop.. Try the below macro..(which I 
> > > > have tried.)
> > > > 
> > > > 
> > > > Sub Macro()
> > > > 
> > > > Dim rngTemp As Range, strPath As String, strFile As String
> > > > 
> > > > strPath = "I:\Ryan\"
> > > > strFile = "Book20.xls"
> > > > 
> > > > Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
> > > > 
> > > > With rngTemp
> > > >     .Formula = "=VLOOKUP(A1,'" & strPath & _
> > > >     "[" & strFile & "]Sheet1'!A:B,2,0)"
> > > >     .Value = .Value
> > > >     .Replace "#N/A", "", xlWhole
> > > > End With
> > > > 
> > > > 
> > > > End Sub
> > > > 
> > > > 
> > > > 
> > > > 
> > > > "ryguy7272" wrote:
> > > > 
> > > > > Sorry for the delay here.  I had to take the CFA this past weekend and was 
> > > > > preoccupied with that, these past few days.  Finally, I can revisit this 
> > > > > project.  Yes, Roger, you are exactly right!  I have data in Column A of 
> > > > > Sheet1 in the active workbook, and want to look up matches from Column A in 
> > > > > another workbook name ‘Test2’, and when there is a match, return the value 
> > > > > that exists in Column B.  I tested your code; it looks good, it 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" <ryguy7272@discussions.microsoft.com> wrote in message 
> > > > > > news:DB565265-3A8C-4938-BB02-CE6E4FE7A108@microsoft.com...
> > > > > > > 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
0
Utf
6/7/2010 3:27:06 PM
You are most welcome and thanks for the feedback.

"ryguy7272" wrote:

> Perfect!!!  I was like, where did 'Jackpot' come from?  Now it makes sense.  
> Jacob, thanks for this, and all the other things too!!
> I appreciate it SOOOOO much!!
> Ryan--
> 
> -- 
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
> 
> 
> "Jackpot" wrote:
> 
> > Try below...(Modify to suit )
> > 
> > With rngTemp
> >     .Formula = "=VLOOKUP(A1,'" & strPath & _
> >     "[" & strFile & "]Sheet1'!A:C,2,0)&VLOOKUP(A1,'" & _
> >     strPath & "[" & strFile & "]Sheet1'!A:C,3,0)"
> >     .Value = .Value
> >     .Replace "#N/A", "", xlWhole
> > End With
> > 
> > 
> > "ryguy7272" wrote:
> > 
> > > One more thing...I haven't see the data table yet, but I heard that ColB and 
> > > ColC will have data, and I need to do a vlookup on BOTH of these columns, and 
> > > pull the matching values into ColB of the Active Workbook.  
> > > 
> > > I tried an If...Then and I tried a couple different loops but couldn't get 
> > > the desired results.  How can I look for matches in either ColB or ColC, and 
> > > pull the results back into the ColB of the Active Workbook?
> > > 
> > > I hope that's doable.
> > > 
> > > Thanks again!!
> > > Ryan--
> > > 
> > > -- 
> > > Ryan---
> > > If this information was helpful, please indicate this by clicking ''Yes''.
> > > 
> > > 
> > > "ryguy7272" wrote:
> > > 
> > > > Thanks so much Jackpot!  That is really slick and it works perfect!!  Just 
> > > > before I read your post, I was actually just toggling back and forth b/w the 
> > > > Locals Window and the Immediate Window, trying to figure out why Roger's code 
> > > > wasn't working for me.  Do you have any idea why that code would not work?  
> > > > 
> > > > This is a HUGE help!  Thanks again!
> > > > Ryan--
> > > > 
> > > > -- 
> > > > Ryan---
> > > > If this information was helpful, please indicate this by clicking ''Yes''.
> > > > 
> > > > 
> > > > "Jackpot" wrote:
> > > > 
> > > > > Hi Ryan
> > > > > 
> > > > > You dont need to open the workbook or loop.. Try the below macro..(which I 
> > > > > have tried.)
> > > > > 
> > > > > 
> > > > > Sub Macro()
> > > > > 
> > > > > Dim rngTemp As Range, strPath As String, strFile As String
> > > > > 
> > > > > strPath = "I:\Ryan\"
> > > > > strFile = "Book20.xls"
> > > > > 
> > > > > Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
> > > > > 
> > > > > With rngTemp
> > > > >     .Formula = "=VLOOKUP(A1,'" & strPath & _
> > > > >     "[" & strFile & "]Sheet1'!A:B,2,0)"
> > > > >     .Value = .Value
> > > > >     .Replace "#N/A", "", xlWhole
> > > > > End With
> > > > > 
> > > > > 
> > > > > End Sub
> > > > > 
> > > > > 
> > > > > 
> > > > > 
> > > > > "ryguy7272" wrote:
> > > > > 
> > > > > > Sorry for the delay here.  I had to take the CFA this past weekend and was 
> > > > > > preoccupied with that, these past few days.  Finally, I can revisit this 
> > > > > > project.  Yes, Roger, you are exactly right!  I have data in Column A of 
> > > > > > Sheet1 in the active workbook, and want to look up matches from Column A in 
> > > > > > another workbook name ‘Test2’, and when there is a match, return the value 
> > > > > > that exists in Column B.  I tested your code; it looks good, it 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" <ryguy7272@discussions.microsoft.com> wrote in message 
> > > > > > > news:DB565265-3A8C-4938-BB02-CE6E4FE7A108@microsoft.com...
> > > > > > > > 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"
> > > > > > > >>
0
Utf
6/7/2010 3:33:26 PM
Ummmm, one more thing, Jacob.  I put some test data in A & B & C, down to row 
65536 (just on excel 2003 now but will upgrade to 2007 very soon).  I get an 
error on this line:
Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)

The error reads:  ‘Run-time error 1004 Method Range of object ‘_Global’ 
failed’.

I tried a couple things, including naming the range, and then I tried this:
Set rngTemp = Range(FullRange)

Still getting the same error.  Ultimately, this project may require 300,000 
rows…  Ugh!  What now?

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


"Jackpot" wrote:

> You are most welcome and thanks for the feedback.
> 
> "ryguy7272" wrote:
> 
> > Perfect!!!  I was like, where did 'Jackpot' come from?  Now it makes sense.  
> > Jacob, thanks for this, and all the other things too!!
> > I appreciate it SOOOOO much!!
> > Ryan--
> > 
> > -- 
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> > 
> > 
> > "Jackpot" wrote:
> > 
> > > Try below...(Modify to suit )
> > > 
> > > With rngTemp
> > >     .Formula = "=VLOOKUP(A1,'" & strPath & _
> > >     "[" & strFile & "]Sheet1'!A:C,2,0)&VLOOKUP(A1,'" & _
> > >     strPath & "[" & strFile & "]Sheet1'!A:C,3,0)"
> > >     .Value = .Value
> > >     .Replace "#N/A", "", xlWhole
> > > End With
> > > 
> > > 
> > > "ryguy7272" wrote:
> > > 
> > > > One more thing...I haven't see the data table yet, but I heard that ColB and 
> > > > ColC will have data, and I need to do a vlookup on BOTH of these columns, and 
> > > > pull the matching values into ColB of the Active Workbook.  
> > > > 
> > > > I tried an If...Then and I tried a couple different loops but couldn't get 
> > > > the desired results.  How can I look for matches in either ColB or ColC, and 
> > > > pull the results back into the ColB of the Active Workbook?
> > > > 
> > > > I hope that's doable.
> > > > 
> > > > Thanks again!!
> > > > Ryan--
> > > > 
> > > > -- 
> > > > Ryan---
> > > > If this information was helpful, please indicate this by clicking ''Yes''.
> > > > 
> > > > 
> > > > "ryguy7272" wrote:
> > > > 
> > > > > Thanks so much Jackpot!  That is really slick and it works perfect!!  Just 
> > > > > before I read your post, I was actually just toggling back and forth b/w the 
> > > > > Locals Window and the Immediate Window, trying to figure out why Roger's code 
> > > > > wasn't working for me.  Do you have any idea why that code would not work?  
> > > > > 
> > > > > This is a HUGE help!  Thanks again!
> > > > > Ryan--
> > > > > 
> > > > > -- 
> > > > > Ryan---
> > > > > If this information was helpful, please indicate this by clicking ''Yes''.
> > > > > 
> > > > > 
> > > > > "Jackpot" wrote:
> > > > > 
> > > > > > Hi Ryan
> > > > > > 
> > > > > > You dont need to open the workbook or loop.. Try the below macro..(which I 
> > > > > > have tried.)
> > > > > > 
> > > > > > 
> > > > > > Sub Macro()
> > > > > > 
> > > > > > Dim rngTemp As Range, strPath As String, strFile As String
> > > > > > 
> > > > > > strPath = "I:\Ryan\"
> > > > > > strFile = "Book20.xls"
> > > > > > 
> > > > > > Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
> > > > > > 
> > > > > > With rngTemp
> > > > > >     .Formula = "=VLOOKUP(A1,'" & strPath & _
> > > > > >     "[" & strFile & "]Sheet1'!A:B,2,0)"
> > > > > >     .Value = .Value
> > > > > >     .Replace "#N/A", "", xlWhole
> > > > > > End With
> > > > > > 
> > > > > > 
> > > > > > End Sub
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > > "ryguy7272" wrote:
> > > > > > 
> > > > > > > Sorry for the delay here.  I had to take the CFA this past weekend and was 
> > > > > > > preoccupied with that, these past few days.  Finally, I can revisit this 
> > > > > > > project.  Yes, Roger, you are exactly right!  I have data in Column A of 
> > > > > > > Sheet1 in the active workbook, and want to look up matches from Column A in 
> > > > > > > another workbook name ‘Test2’, and when there is a match, return the value 
> > > > > > > that exists in Column B.  I tested your code; it looks good, it 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" <ryguy7272@discussions.microsoft.com> wrote in message 
> > > > > > > > news:DB565265-3A8C-4938-BB02-CE6E4FE7A108@microsoft.com...
> > > > > > > > > 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
0
Utf
6/7/2010 4:02:42 PM
There seems to be a limit at about 10,000 rows or so.  After that I get a 
message that reads 'Excel cannot complete this task with available resources. 
 Choose less data or close other applications.'  
When I click Debug, the following line is yellow:
..Formula = "=VLOOKUP(A1,'" & strPath & "[" & strFile & "]Sheet1 
'!A:C,2,0)&VLOOKUP(A1,'" & strPath & "[" & strFile & "]Sheet1'!A:C,3,0)"

Is there an easy workaround for this, or do I need to try something else, 
like maybe Access?  I'm totally comfortable working with Access, but I though 
it would be easier to do this in Excel.  It's turning out to be not so easy, 
actually.


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


"ryguy7272" wrote:

> Ummmm, one more thing, Jacob.  I put some test data in A & B & C, down to row 
> 65536 (just on excel 2003 now but will upgrade to 2007 very soon).  I get an 
> error on this line:
> Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
> 
> The error reads:  ‘Run-time error 1004 Method Range of object ‘_Global’ 
> failed’.
> 
> I tried a couple things, including naming the range, and then I tried this:
> Set rngTemp = Range(FullRange)
> 
> Still getting the same error.  Ultimately, this project may require 300,000 
> rows…  Ugh!  What now?
> 
> -- 
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
> 
> 
> "Jackpot" wrote:
> 
> > You are most welcome and thanks for the feedback.
> > 
> > "ryguy7272" wrote:
> > 
> > > Perfect!!!  I was like, where did 'Jackpot' come from?  Now it makes sense.  
> > > Jacob, thanks for this, and all the other things too!!
> > > I appreciate it SOOOOO much!!
> > > Ryan--
> > > 
> > > -- 
> > > Ryan---
> > > If this information was helpful, please indicate this by clicking ''Yes''.
> > > 
> > > 
> > > "Jackpot" wrote:
> > > 
> > > > Try below...(Modify to suit )
> > > > 
> > > > With rngTemp
> > > >     .Formula = "=VLOOKUP(A1,'" & strPath & _
> > > >     "[" & strFile & "]Sheet1'!A:C,2,0)&VLOOKUP(A1,'" & _
> > > >     strPath & "[" & strFile & "]Sheet1'!A:C,3,0)"
> > > >     .Value = .Value
> > > >     .Replace "#N/A", "", xlWhole
> > > > End With
> > > > 
> > > > 
> > > > "ryguy7272" wrote:
> > > > 
> > > > > One more thing...I haven't see the data table yet, but I heard that ColB and 
> > > > > ColC will have data, and I need to do a vlookup on BOTH of these columns, and 
> > > > > pull the matching values into ColB of the Active Workbook.  
> > > > > 
> > > > > I tried an If...Then and I tried a couple different loops but couldn't get 
> > > > > the desired results.  How can I look for matches in either ColB or ColC, and 
> > > > > pull the results back into the ColB of the Active Workbook?
> > > > > 
> > > > > I hope that's doable.
> > > > > 
> > > > > Thanks again!!
> > > > > Ryan--
> > > > > 
> > > > > -- 
> > > > > Ryan---
> > > > > If this information was helpful, please indicate this by clicking ''Yes''.
> > > > > 
> > > > > 
> > > > > "ryguy7272" wrote:
> > > > > 
> > > > > > Thanks so much Jackpot!  That is really slick and it works perfect!!  Just 
> > > > > > before I read your post, I was actually just toggling back and forth b/w the 
> > > > > > Locals Window and the Immediate Window, trying to figure out why Roger's code 
> > > > > > wasn't working for me.  Do you have any idea why that code would not work?  
> > > > > > 
> > > > > > This is a HUGE help!  Thanks again!
> > > > > > Ryan--
> > > > > > 
> > > > > > -- 
> > > > > > Ryan---
> > > > > > If this information was helpful, please indicate this by clicking ''Yes''.
> > > > > > 
> > > > > > 
> > > > > > "Jackpot" wrote:
> > > > > > 
> > > > > > > Hi Ryan
> > > > > > > 
> > > > > > > You dont need to open the workbook or loop.. Try the below macro..(which I 
> > > > > > > have tried.)
> > > > > > > 
> > > > > > > 
> > > > > > > Sub Macro()
> > > > > > > 
> > > > > > > Dim rngTemp As Range, strPath As String, strFile As String
> > > > > > > 
> > > > > > > strPath = "I:\Ryan\"
> > > > > > > strFile = "Book20.xls"
> > > > > > > 
> > > > > > > Set rngTemp = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
> > > > > > > 
> > > > > > > With rngTemp
> > > > > > >     .Formula = "=VLOOKUP(A1,'" & strPath & _
> > > > > > >     "[" & strFile & "]Sheet1'!A:B,2,0)"
> > > > > > >     .Value = .Value
> > > > > > >     .Replace "#N/A", "", xlWhole
> > > > > > > End With
> > > > > > > 
> > > > > > > 
> > > > > > > End Sub
> > > > > > > 
> > > > > > > 
> > > > > > > 
> > > > > > > 
> > > > > > > "ryguy7272" wrote:
> > > > > > > 
> > > > > > > > Sorry for the delay here.  I had to take the CFA this past weekend and was 
> > > > > > > > preoccupied with that, these past few days.  Finally, I can revisit this 
> > > > > > > > project.  Yes, Roger, you are exactly right!  I have data in Column A of 
> > > > > > > > Sheet1 in the active workbook, and want to look up matches from Column A in 
> > > > > > > > another workbook name ‘Test2’, and when there is a match, return the value 
> > > > > > > > that exists in Column B.  I tested your code; it looks good, it 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" <ryguy7272@discussions.microsoft.com> wrote in message 
> > > > > > > > > news:DB565265-3A8C-4938-BB02-CE6E4FE7A108@microsoft.com...
> > > > > > > > > > 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?
> > > > > > > > > >
0
Utf
6/7/2010 4:56:36 PM
Hi Jacob

An interesting approach to let the Vlookup run without any pre-testing, then 
Replace the #N/A's.
Very neat!

-- 

Regards
Roger Govier

"Jackpot" <Jackpot@discussions.microsoft.com> wrote in message 
news:ABC7BF81-92BA-481D-AE8D-053C8CC05706@microsoft.com...
> 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" <ryguy7272@discussions.microsoft.com> wrote in message
>> > news:DB565265-3A8C-4938-BB02-CE6E4FE7A108@microsoft.com...
>> > > 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
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 5180 (20100607) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

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

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
6/7/2010 5:04:54 PM
Reply:

Similar Artilces:

error when copying PST folder into Exchange 2000 mailbox
Hi, I have an old PST file. Scanpst.exe has been run and a couple of errorr repaired. I can open the PST fine in Outlook 2003 SP3. When I try to copy a folder from the PST into an Exchange 2003 mailbox, I see this error: Unable to move or copy folders. Can't copy folder. A top-Level folder can't be copied to one of its subfolders. Of, you may not have appropriate permissions for the folder. To check your permissions for the folder, right-click the folder, and then click Properties on the shortcut menu. The folder and the items actually copies OK, but Outlook displays this error. W...

Copy Form Text
Hi, Can anyone tell me how to copy text from a form in word 2003 so as I can paste the text to another file, I can't select any text as it's in a form so I have created a command button to copy text to clipboard but can someone help with a macro to attach to the button to copy all text in document. Regards Mark If you are trying to copy an entire form, using the Insert File dialog box will be the easiest solution. Create a new document. Click Insert | File. Locate the protected document and click it. Then click the Insert button. -- Stefan Blom Microsoft Wo...

How to make two codes "Worksheet_Change" work together in same sheet code page
Friends, Please, anybody knows how to make these two VB codes work together? When I put them together in the same "sheet code page" in VBA, th second one doesn't work. Why? *** Code 1 *** Private Sub Worksheet_Change(ByVal Target As Range) * * On Error GoTo QuitCode * * If Intersect(Target, Range("c1:c15")) Is Nothing Then * * * * Exit Sub * * ElseIf Target.Value <>*"" And Target.Offset(0, -1).Value = "" Then * * * * MsgBox "You haven't typed the name of the client yet." * * * * Target.Offset(0, -1).Activate End If QuitCode: ...

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

stop my speadsheet from creating an automatic backup copy
I have a spreadsheet I work with on a daily basis. Every morning I access it, there is a backup copy of this spreadsheet in my folder where I keep this spreadsheet. I want this to stop. thanks! Linda G. If the name of the workbook has a .xlk extension, then try this: File|SaveAs|Click on Tools|General Options|Uncheck "always create backup" (This is the path for xl2002+. For xl2k, I think it was just "options"--not "general Options". lggallo wrote: > > I have a spreadsheet I work with on a daily basis. Every morning I access > it, there is a b...

copy paste value
I have a colum of vlookup numeric data and want to add it up, but each cell may not return a value depending on how many items are needed. I belive I will need to add a colum in and grab the numeric data that is returned and then use a formula to do a copy paste value and if no value equal zero. I am not sure how to do this thoe. here is the vlookup formula i am using. Can you please help =IF(ISNA(VLOOKUP(A3,'Inventory Items'!$A$3:$C$888,'Inventory Items'!$C$1,FALSE)),"",(VLOOKUP(A3,'Inventory Items'!$A$3:$C$888,'Inventory Items'!$C$1,...

Two Email Accounts not able to reply out of one of them
Hello All, I was wondering if someone here has ran into this problem. I currently have two email accounts setup in Exchange I can receive email in both of these accounts and view the email in Outlook 2003. In my default account if I receive an email I can reply back. But in my secondary mailbox if I receive an email I cannot reply back I get a error message which is listed below. On the secondary account I have added my username under permissions and gave it full access but I am still getting the below error. Anything will help thanks. Your message did not reach some or all of the inten...

How do I build a workbook from the worksheets another workbook?
Is it possible to make up the sheets of an Excel workbook from another workbook that is in a remote address (i.e. a folder below) If you're asking whether you can copy (or move) those sheets from that remote workbook into another workbook, then the answer is yes. If you're asking if a workbook can contain worksheets of another workbook, then the answer is no. Rico wrote: > > Is it possible to make up the sheets of an Excel workbook from another > workbook that is in a remote address (i.e. a folder below) -- Dave Peterson Thanks Dave, I know you can populate cells from...

have same copy 646 error
can you help me solve this problem If the OS is not Vista and the update in question is not KB982331, then please post more information so that someone *may* assist you: How to ask a question http://support.microsoft.com/kb/555375 If the OS is Vista and the update in question KB982331, as posted previously by Engel, from a post by an MS Support Engineer in the social.answers.update forum: http://social.answers.microsoft.com/Forums/en-US/vistawu/thread/08fec454-aad4-49fd-8df9-068b00faef6b 1. Turn off UAC. Don't just lower down the restrictions - turn it off completely. ...

Copy Purchase orders #2
Does any one know how to copy a purchase order? We do booking orders and have different ship dates. I would like to break up my PO and not have to enter items 2 - 3 times. Thanks No-one answered this one yet. Shame, because I could do with knowing. We very often want to produce a PO for 8 branch, all identical. Please help. "lax guy" <laxguy@discussions.microsoft.com> wrote in message news:C284C5BF-7A6E-45CA-A9A0-88871B48E3B0@microsoft.com... > Does any one know how to copy a purchase order? > > We do booking orders and have different ship dates. I would like...

make copies of outgoing mail
Hi, I've Win2k3 on which I've Exch2k3 installed. Is it possible for each outgoing mail (no matter from which mailbox) to have a copy into one central mailbox? (Collect all outgoing messages (including attachments) into 1 mailbox) Thanks in advance. B.M. The best you can do here (natively) would be to enable Exchange Archiving/Journaling on the properties of the Mailbox store. However, this will create a copy of all outgoing AND incoming e-mails for users on that mailbox store. If you want more granular functionality, you would need to look into some sort of 3rd party archiv...

copy worksheet
How can I copy a sheet entitled "Planning" as the first sheet in a workbook name "tmplt_planning.xlsm" which is in the same folder as the current workbook? Thank you QB Hi there, You could use something like this ... Option Explicit Sub Copy_Planning_Worksheet() Dim wb As Workbook, wbDest As Workbook, ws As Worksheet Dim bWBOpen As Boolean, sName As String Set wb = ThisWorkbook If WSEXISTS("Planning", wb) = False Then MsgBox "Worksheet (Planning) was not found in this workbook!", vbExclamation, "ERROR!" Exit Sub E...

copying names of folders
hi, i wold like to copy all the folders i one personal folders to another but only the names of the folders and not what's in the folders. is it possible? thanks michal ...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

How can I create a rule to copy OUTGOING mail to a specific folde.
I want to have e-mails that I send to various recipients automatically routed to folders containing all my correspondence with them. So far, when I try to create such a rule, it only ends up applying to INCOMING mail, not outgoing. What am I missing? You first. What version of Outlook? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, ckanis asked: | I want to have e-mails that I send to va...

how to create formula to divide two rows autoaatically
Is there any way to setup a sheet or create a formula so that it will divide the data in column A by Column B anytime the data is entered and put it into columnC ? What I am trying to do is create a spreadsheet for calculating fuel MPG. So I have columns as miles, gallons and the calculation as MPG. What I want to do is anytime a value is entered into miles and gallons, to calculate mpg and put it into that respective cell. Is there any way I can do this ? Thanks -- Tony Tony, in Column C, type the formula =sum(a1/b1). This should give you the result you are looking for. Hop...

Two databases or one
Hi, What if you have two organizations and you plan to design a database to do the same task but within that task, the defined tables will vary because of unique qualities related to the individual organization. Would it be best to create separate databases for each organization or keep the two organizations together? Anyone know the guidelines related to this? Thanks for any feedback. Ask yourself this - How often will you need the combined data? -- Build a little, test a little. "AccessKay" wrote: > Hi, > > What if you have two o...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

Pasting Pictures
I copy hundreds of excel charts into powerpoint presentations. I always use "paste as picture". I just got a new widescreen monitor. Now when I paste a chart from excel to powerpoint, the chart is wider and taller than to powerpoint slide. I have to shrink the image way down to fit on the slide by dragging the edges. Normally, this would be no big deal to do once or twice a day. But this is killing me to do it hundreds of times. Anybody solve this problem? thanks! An update to the following: The monitor is not the problem. I have a co-worker with the same pr...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

Copy filename from Excel 2010 Recent Workbooks to Clipboard
I often reuse recently opened workbooks in SQL Server Management Studio/SSIS. Please add the ability to copy a workbook name to the clipboard in the "Recent Workbooks" right click. Can this be included in a Windows Update? PS Love Excel 2010: great improvement and work! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web...

Referencing two (or more) cell values in formula
Hi, Can anyone tell me the correct syntax for referencing two cells as a criterion in a formula. For example if I want to sum cells in b1:b5 if cells a1:a5 are greater than the value in c2 I would write the following: =sumif(a1:a5,">"&c2,b1:b5) If I want to sum b1:b5 if cells in a1:a5 are greater than the value in c2 and less than c3 what should I write? =sumif(a1:a5,and(">"&c2,"<"&c3),b1:b5) this doesn't work and neither does this.... =sumif(a1:a5,and(>c2,<c3),b1:b5). Any help gratefully received. Thanks, Will willcull@...

I need to compare to columns and indicate the matches in another
am wanting to compare 2 columns for exact matching. If there are two matching items I want to be able to say "A match" in a chosen cell for all the ones that match. Column C will be retrived in an random order... so how would I write the formula for that????? Example: A B C 45time 11tune A match 11tune 89time 47doog 43jkjkj A match 123ABC 123ABC If possible include how to highlight the ones that make as another option. Thanks Try something like this: For a value list in B1:B5 and a ...

Number of copies when printing
Can anybody help...? We are currently printing off a form that has been duplicated many times to obtain answers to many questions in a way of collecting clients requirements. The problem is that whenever we print the form with the answers, the default setting is 10 copies. This is not the default setting on the printer and all other documents from word, outlook etc, print just one copy. Is there a setting somewhere within the excel file that is forcing the 10 copies...? We did not generate the form originally and the company who did are unable to answer our question. Any help would be app...

Advice on comparing data sets
Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...