Runtime Error 1004 - Method Rows object_global failed

[tommyb]

Hi!

Im having a very wierd problem. I've got an excel spreadsheet that has a search function on it. There is a hidden tab where all the data sits, and a visible tab with a search button where you can enter a reg of a car and it brings up details from the hidden tab.

This works perfectly when I open the excel file that is on my machine. I've put the excel file on the intranet, and to open it you click a link, and when the pop up box comes up, you click 'OPEN', and the file opens up within IE. When I try and search for a reg, I get the following error!

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>Run-time error '1004':
Method '<NAME method of>' of object '_Global' failed </o>
<o></o>
<o>Its really confusing because this didnt used to happen before.. so I cant see why there would be an error with the code because it used to work!</o>
<o></o>
<o></o>
<o>Here is my code.</o>
<o></o>
<o></o>
<o></o>
<o></o>
<o>

[COLOR=navy][FONT=Comic Sans MS]<o:p>Sub Button1_Click()[/FONT][/COLOR]
[FONT=Comic Sans MS][COLOR=navy]'Sheet module code.[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]'Find my data in the indicated range, on this sheet![/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Dim strMessage$, strTitle$, strDefault$, strShtNm$[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Dim lngMyCol&, lngLabelRow&[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Dim varRegID As Variant[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Dim rngMyData As Range[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Dim cell As Object</o:p>[/COLOR][/FONT]
[COLOR=navy][FONT=Comic Sans MS]<o:p>[/FONT][/COLOR]
[FONT=Comic Sans MS][COLOR=navy]Application.ScreenUpdating = False[/COLOR][/FONT]
 
[FONT=Comic Sans MS][COLOR=navy]strShtNm = ActiveSheet.Name[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]strMessage = "Enter ""Reg"" ID, below:" ' Set Prompt.[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]strTitle = "Find This Information!" ' Set Title.[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]strDefault = "" ' Set Default.[/COLOR][/FONT]
 
[FONT=Comic Sans MS][COLOR=navy]' Display strMessage, strTitle, and strDefault value.[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]varRegID = InputBox(strMessage, strTitle, strDefault)[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]'The row that your data's labels are in![/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]lngLabelRow = 2[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]'Change below to your range, the Column to find data in![/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]lngLstRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Sheets("Sheet1").Activate[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Sheets("Sheet1").Range("A2:A" & lngLstRow).Activate[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]On Error GoTo myErr[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]'Find the data![/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Selection.Find(What:=varRegID, _[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]After:=ActiveCell, LookIn:=xlValues, LookAt:= _[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]MatchCase:=False, SearchFormat:=False).Activate[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]'Get row data![/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]lngMyCol = ActiveCell.Column[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]lngMyRow = ActiveCell.Row[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]lngLstCol = Sheets("Sheet1").Cells(lngMyRow, Columns.Count).End(xlToLeft).Column[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Set rngMyData = Sheets("Sheet1").Range(Cells(lngMyRow, 1), Cells(lngMyRow, lngLstCol))[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]For Each cell In rngMyData[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]lngLabelCol = lngLabelCol + 1[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Sheets("Sheet2").Cells(16, lngLabelCol).Value = cell.Value[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Next cell[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Sheets("Sheet1").Range("A1").Activate[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Sheets(strShtNm).Select[/COLOR][/FONT]
 
[FONT=Comic Sans MS][COLOR=navy]GoTo myEnd[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]myErr:[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Sheets("Sheet1").Range("A1").Activate[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Sheets(strShtNm).Select[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]MsgBox "The ""Reg"" ID:" & vbLf & vbLf & _[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]varRegID & vbLf & vbLf & _[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]"was not found!", _[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]vbCritical + vbOKOnly, _[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]"Search Error!"[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]myEnd:[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]Application.ScreenUpdating = True[/COLOR][/FONT]
[FONT=Comic Sans MS][COLOR=navy]End Sub[/COLOR][/FONT]</o:p>

</o>
<o></o>
<o>Please help!!

Thanks!</o>

 

[tommyb]

It works!!! Thank you so much!

Why was it that it used to work but randomly stopped working??