MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Why does this fail on other computers

Posted by John on January 30, 2002 6:57 AM

Can you see any reason why this macro would fail on other computers except mine. I have a network file called "Parts ordered.xls" that other computers access by a shortcut on thier desktop and it opens fine but when the macro launches it errors out on Workbooks("parts ordered").Sheets("sheet1").Activate Any help that you have would be greatly appreciated.

Sub findit()
Dim invar As String
invar =
Dim c
Dim ctr
Dim testy
Dim actcnt
testy = 0
ctr = 1
Workbooks("parts ordered").Sheets("sheet1").Activate
Workbooks("parts ordered").Sheets("sheet1").Range("a1").Select
actcnt = Workbooks("parts ordered").Sheets("sheet1").Cells(1, 1).CurrentRegion.Count
Workbooks("parts ordered").Sheets("sheet2").Activate
Workbooks("parts ordered").Sheets("sheet2").Range("A1:H" & actcnt).Value = ""
With Workbooks("parts ordered.xls").Worksheets("sheet1").Range("A1:H500")
Set c = .Find(invar, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
testy = c.Row
Sheets(2).Range("A" & ctr).Value = Sheets(1).Range("A" & testy)
Sheets(2).Range("B" & ctr).Value = Sheets(1).Range("B" & testy)
Sheets(2).Range("C" & ctr).Value = Sheets(1).Range("C" & testy)
Sheets(2).Range("D" & ctr).Value = Sheets(1).Range("D" & testy)
Sheets(2).Range("E" & ctr).Value = Sheets(1).Range("E" & testy)
Sheets(2).Range("F" & ctr).Value = Sheets(1).Range("F" & testy)
Sheets(2).Range("G" & ctr).Value = Sheets(1).Range("G" & testy)
Sheets(2).Range("H" & ctr).Value = Sheets(1).Range("H" & testy)

ctr = ctr + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

Posted by JohnG on January 30, 2002 7:17 AM

Excel is probaly looking at the wrong computer. You need to make sure that from where ever the program is called it knows where to look for the file. tr ctr = ctr + 1 Set c = .FindNext(c)