Simple Run Time

[JTL9161]

I've been working on this macro most of day and when I finally get it basically work towards the end I get a run-time error 424 "Object Req." when I am selecting the entire work sheet (cells.select) before I do a replace.

I must be missing something simple because I can't figure what DIM for RANGE to get it past it.

Cells.Select <-----------------------Error!
Selection.Replace What:="18", Replacement:="2018", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Any simple suggestions??

Thanks,
James

 

[Joe4]

You are getting an error on the "Cells.Select" line?
Are you protecting/locking any cells so that they cannot be selected?

 

[JTL9161]

Needed to run it again to make sure it was at that point.

Yes. The debug take me to the Cell.select.

 

[Joe4]

Your image posting attempt failed. There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

In what module have you placed your VBA code?

What happens if you just try to run this one line code?

Sub Test()
    Cells.Select
End Sub

 

[joeu2004]

I am selecting the entire work sheet (cells.select) before I do a replace.

Not a good idea, by the way. Better: Activesheet.UsedRange.Select .

Of course, that is not likely to solve your problem, except by coincidence.

I cannot image why Cells.Select would fail. Note that the following works just fine (for me):

Sub doit()
Cells.Select
MsgBox Selection.Address
End Sub

I suspect that there is something in the context that you are not sharing. For example, I wonder if the statement before that is not properly terminated.

 

[JTL9161]

It must be something previous as I removed the cells.select and then the next function got the same error.

The macro I wrote copies a number from the spreadsheet into a WRQ IBM 5250 Terminal Session which is our in-house data base. It pastes the number and then executes the search for the company the number is associated with. I then does several screen scrapes copying each piece of data back to the spreadsheet and so on. Here is the top half of the macro. Each CALL function then executes code farther down in the macro. What the simple thing I am trying to do is just replace the 18's in the dates to 2018 then eventually copy all the scraped data to another spreadsheet.

I think the error maybe because the macro originates from the WRQ session (where the macro is installed and executed) the when I tell it to do the next function in the spreadsheet it is still in the WRQ session. I think I need to get the macro to move over to the spreadsheet and then execute the replacing of the dates. Otherwise I need to just write a separate macro to do that.

Dim lRow As Long, lLastRow As Long
Dim oMyWorkBook As Object
Dim oMyWorkSheet As Object
'Set Currentcell = ActiveCell
'Dim rng As Range

On Error Resume Next
Set oMyWorkBook = GetObject("C:\Users\jleonar3\Documents\DisCompare\MMDDYY-SGCS.XLSM")
Set oMyWorkSheet = oMyWorkBook.Sheets("sheet1")
On Error GoTo 0


If oMyWorkSheet Is Nothing Then
MsgBox "Source workbook not found."
Exit Sub
End If

With oMyWorkSheet
.Activate

'--find last row of data in col B. xlUp = -4162
lLastRow = .Cells(.Rows.Count, "B").End(-4162).Row

'--step through each row
For lRow = 2 To lLastRow
'--paste policy number onto clipboard
.Cells(lRow, "B").Copy

'--call procedure that will lookup corresponding network number
' and paste it to clipboard
Call DoBPLTerminalSession

'--paste network number into columm C
.Cells(lRow, "C").Select
.Paste

Call DoSTARTTerminalSession
.Cells(lRow, "D").Select
.Paste

Call DoENDTerminalSession
.Cells(lRow, "E").Select
.Paste

Call DoPLANTerminalSession
.Cells(lRow, "F").Select
.Paste

Call DoTRANSDATETerminalSession
.Cells(lRow, "G").Select
.Paste

Call DoSTATETerminalSession
.Cells(lRow, "I").Select
.Paste

Next lRow


End With

oMyWorkBook.Activate
'ActiveSheet.UsedRange.Select

(macro stop here with Run time 424 error)

Selection.Replace What:="18", Replacement:="2018", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'Selection.Replace What:="17", Replacement:="2017", LookAt:=xlPart, _
'SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
'ReplaceFormat:=False
'Range("A1").Select
End Sub

 

[joeu2004]

It must be something previous as I removed the cells.select and then the next function got the same error.

Sorry, but nothing obviously wrong leaps off the page for me.

I'm not sure the following works:

With oMyWorkSheet
   .Activate

You might need to precede that with oMyWorkBook.Activate. No time to test.

One minor issue, unrelated: .Cells(.Rows.Count, "B").End(-4162).Row is not "last row of data in col B. xlUp = -4162" per se.

Instead, it will always be 1044414 (1048576 - 4162) for an xlsm file.

Sorry, gotta run!