Simple Run Time

JTL9161

Active Member
Joined
Aug 29, 2012
Messages
345
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
You are getting an error on the "Cells.Select" line?
Are you protecting/locking any cells so that they cannot be selected?
 

JTL9161

Active Member
Joined
Aug 29, 2012
Messages
345
Needed to run it again to make sure it was at that point.

Yes. The debug take me to the Cell.select.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
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?
Code:
Sub Test()
    Cells.Select
End Sub

 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,975
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited:

JTL9161

Active Member
Joined
Aug 29, 2012
Messages
345
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,975
Office Version
  1. 2010
Platform
  1. Windows
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:

Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,955
Messages
5,599,040
Members
414,280
Latest member
morralletti

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top