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]

By the way, when I debug.. it points to the following line..

lngLstRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

I have no idea why its wrong because it used to work!

 

[Richard Schollar]

Hi

Try it like this:

With Sheets("Sheet1")
  lngLstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

 

[tommyb]

Doesnt work

Im really stumped on why it works on my desktop, but not when its ran through the intranet!

 

[Richard Schollar]

It fails on this line - ie it gets highlighted in yellow by the debugger?

lngLstRow = .Cells(.Rows.Count, 1).End(xlUp).Row

You did spot the addition of the period (in red) before the Rows?

 

[tommyb]

I have included the dot (I just pasted what you wrote on top of what I had. I debugged it again.. and now its highlighting this line yellow..

With Sheets("Sheet1")

It still works on my desktop, just not on the intranet!

 

[RoryA]

Try fully qualifying everything - so for the line you mentioned you would want to use either Thisworkbook.Sheets("Sheet1") or Activeworkbook.Sheets("Sheet1")

 

[Richard Schollar]

Does it help if you prefix the code with:

With ThisWorkbook.Sheets("Sheet1")

amemdment is in red.

 

[Richard Schollar]

Bugger. That just makes me look like I'm copying you Archibald.

 

[RoryA]

Not the first time today.