Finding data in another workbook
 

I have an excel file that I plan to put a button in which will open another
excel file and find certain items in the new file but am having some
problems. Here is the code I have:

Dim Row As Byte, Item As String, Item2 As String, LR1 As Long, LR2 As Long
Dim destWB As Workbook

If Range("D" & Row) = 1 Then
Item = "Beacons"
Do
Item2 = destWB.Worksheets("Sheet1").Range("E" &
LR2).Value
LR2 = LR2 - 1
Loop Until Item2 = Item
destWB.Worksheets("Sheet1").Range("J" & LR2) =
destWB.Worksheets("Sheet1").Range("J" & LR2) + 2
End if

I keep getting a Run Time error 1004-Application Defined or Object Defined
Error on the line-Item2 = destWB.Worksheets("Sheet1").Range("E" & LR2).Value

Is there a better way to write this?
I want LR2 to be the row number on the code-opened worksheet in which cell E
& LR2 (where LR2 is the looped variable.) is identical to either Item or a
cell on the current workbook. I can make the latter happen but this part of
the code requires me to set Item's value in the code. Thank you in advance
for any insight.

Finding data in another workbook
 

Hi,
destWB is not set to anything in your code. (but maybe you just omitted part
of the code in this post to make it shorter).
say the other book is 'mybook.xls' and assuming that it is open. Do a Set
right after the variables declariations.
Set destWB=Workbooks("mybook.xls")
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"JCanyoneer" wrote:

I have an excel file that I plan to put a button in which will open another
excel file and find certain items in the new file but am having some
problems. Here is the code I have:

Dim Row As Byte, Item As String, Item2 As String, LR1 As Long, LR2 As Long
Dim destWB As Workbook

If Range("D" & Row) = 1 Then
Item = "Beacons"
Do
Item2 = destWB.Worksheets("Sheet1").Range("E" &
LR2).Value
LR2 = LR2 - 1
Loop Until Item2 = Item
destWB.Worksheets("Sheet1").Range("J" & LR2) =
destWB.Worksheets("Sheet1").Range("J" & LR2) + 2
End if

I keep getting a Run Time error 1004-Application Defined or Object Defined
Error on the line-Item2 = destWB.Worksheets("Sheet1").Range("E" & LR2).Value

Is there a better way to write this?
I want LR2 to be the row number on the code-opened worksheet in which cell E
& LR2 (where LR2 is the looped variable.) is identical to either Item or a
cell on the current workbook. I can make the latter happen but this part of
the code requires me to set Item's value in the code. Thank you in advance
for any insight.

Finding data in another workbook
 

Here is my set Statement, just left it out for space reasons. An ideas on my
main question?

If bIsBookOpen("Inventory Check List-Test.xls") Then
Set destWB = Workbooks("Inventory Check List-Test.xls")
Else
Set destWB = Workbooks.Open("J:\My Documents\APS Bodies &
Options\2006\Inventory Check List-Test.xls")
End If


"JCanyoneer" wrote:

I have an excel file that I plan to put a button in which will open another
excel file and find certain items in the new file but am having some
problems. Here is the code I have:

Dim Row As Byte, Item As String, Item2 As String, LR1 As Long, LR2 As Long
Dim destWB As Workbook

If Range("D" & Row) = 1 Then
Item = "Beacons"
Do
Item2 = destWB.Worksheets("Sheet1").Range("E" &
LR2).Value
LR2 = LR2 - 1
Loop Until Item2 = Item
destWB.Worksheets("Sheet1").Range("J" & LR2) =
destWB.Worksheets("Sheet1").Range("J" & LR2) + 2
End if

I keep getting a Run Time error 1004-Application Defined or Object Defined
Error on the line-Item2 = destWB.Worksheets("Sheet1").Range("E" & LR2).Value

Is there a better way to write this?
I want LR2 to be the row number on the code-opened worksheet in which cell E
& LR2 (where LR2 is the looped variable.) is identical to either Item or a
cell on the current workbook. I can make the latter happen but this part of
the code requires me to set Item's value in the code. Thank you in advance
for any insight.

Finding data in another workbook
 

Does it error the first time it passes in the loop or aftre a while. In the
latyer case case it could be that LR2=0. So you could replace
Loop Until Item2 = Item
By
Loop Until Item2 = Item or LR2=0

Also, what returns the following line when inserted right before error line':
debug.print Item2, LR2
(it will return in the Immediate window 1 for each iteration of the loop so
look at the values last returned, right before the error).

Finally it seems like you compare to "Beacons" and your comparison is case
sensitive. Maybe it doesn't applky to your specific case, but instead of
Item2 = destWB.Worksheets("Sheet1").Range("E" &
LR2).Value
i would compare UCase(Item2) with
UCase(Trim(destWB.Worksheets("Sheet1").Range("E" &
LR2).Value))
Or could you be searching the wrong range?

--- could be that Item2 is not found for some reasons so LR2 ends up being
0 with fails in Range(E & LR2). The above suggestions should fix this issue
if it comes from a comparison problem.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"JCanyoneer" wrote:

Here is my set Statement, just left it out for space reasons. An ideas on my
main question?

If bIsBookOpen("Inventory Check List-Test.xls") Then
Set destWB = Workbooks("Inventory Check List-Test.xls")
Else
Set destWB = Workbooks.Open("J:\My Documents\APS Bodies &
Options\2006\Inventory Check List-Test.xls")
End If


"JCanyoneer" wrote:

I have an excel file that I plan to put a button in which will open another
excel file and find certain items in the new file but am having some
problems. Here is the code I have:

Dim Row As Byte, Item As String, Item2 As String, LR1 As Long, LR2 As Long
Dim destWB As Workbook

If Range("D" & Row) = 1 Then
Item = "Beacons"
Do
Item2 = destWB.Worksheets("Sheet1").Range("E" &
LR2).Value
LR2 = LR2 - 1
Loop Until Item2 = Item
destWB.Worksheets("Sheet1").Range("J" & LR2) =
destWB.Worksheets("Sheet1").Range("J" & LR2) + 2
End if

I keep getting a Run Time error 1004-Application Defined or Object Defined
Error on the line-Item2 = destWB.Worksheets("Sheet1").Range("E" & LR2).Value

Is there a better way to write this?
I want LR2 to be the row number on the code-opened worksheet in which cell E
& LR2 (where LR2 is the looped variable.) is identical to either Item or a
cell on the current workbook. I can make the latter happen but this part of
the code requires me to set Item's value in the code. Thank you in advance
for any insight.

Finding data in another workbook
 

and once again , i forgot to reply to your main question...

instead of a loop, if you need to find the first instance of item2 in
destWB, you could use the FInd method of the Range object:

Dim rg As Range, item2 as string, rg as range
..
..
..
'search in whole column E
Set rg = destWB.Worksheets("Sheet1").Range("E:E").Find(item 2, , xlValues,
xlWhole)
If rg Is Nothing Then
MsgBox "not found"
Else
MsgBox "found at " & rg.Address
End If

--
Regards,
Sébastien
<http://www.ondemandanalysis.com