Run Time Error 91, object variable not set??

nickadeemus2002

New Member
Joined
Feb 18, 2004
Messages
41
I need some more help please. I have a simple procedure that asks the user for two inputs, the worksheet name and the range of worksheet, then identifies that blanks and pastes them with blue cells in a new worksheet. I keep getting the run time error when the user inputs the range. How do I resolve this. I've been working on it this morning and I am still analyzing it. Suggestions??

Code:

'This routine will find the blank cells in a
'worksheet and highlight them in a new worksheet.
'
Sub Copy2NewWorksheet()

Dim oOrgRng, oRng As Range
Dim oRngEnd As Range
Dim oSheetName As String
Dim oNewRange, oNewBlankRange As Range
Dim oSht As Worksheet

'Prompt user for Sheet Name
oSheetName = InputBox("Enter Name of Worksheet to inspect.")

'Prompt user for Last Cell
o_O oRngEnd = InputBox("Enter the range of Worksheet to inspect.")

Set oOrgRng = Sheets(oSheetName).Range(oRngEnd)

Set oRng = oOrgRng.SpecialCells(xlCellTypeBlanks)

'if there is no blank cells found, exit this sub directly
If oRng.Count > 0 Then

'Create one new sheet
Set oSht = Sheets.Add

'name new sheet Blanks;
oSht.Name = "blanks"

'select the source range
Sheets(oSheetName).Select
oOrgRng.Select

'perform the copy
Application.CutCopyMode = False
Selection.Copy

'select the new created blanks sheet
Sheets("blanks").Select

'select one cell to star the paste
Call Range("A1").PasteSpecial(xlPasteAll)
Set oNewRange = Selection

Set oNewBlankRange = oNewRange.SpecialCells(xlCellTypeBlanks)

' I set the interior color of these blankcells to blue
oNewBlankRange.Interior.Color = RGB(0, 0, 255)

'Since the contents from the source range has also
'been copied to the new created sheet, clear the source range
oNewRange.ClearContents

End If

End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
I believe that
Dim oRngEnd As Range
should be
Dim oRngEnd As String

You are setting equal to a value in an Input Box and are referencing it using Range(oRngEnd). I believe that both of those situations require it to be dimensioned as a String.
 

nickadeemus2002

New Member
Joined
Feb 18, 2004
Messages
41
JMiskey,

Thanks. I did that and it solved that error. Now I have an Error 9, subsript out of range. I entered "A1,E10", "A1:E10" into the input box and got the error. Any suggestions?

Thanks

Chris
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
I'm a little confused...

Did you enter "A1,E10" or "A1:E10" into your InputBox? Or do you mean that you tried both and neither worked?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

If you use Excel's InputBox method instead of VBA's InputBox function, you can prompt for a Range like this:

Code:
On Error Goto Cancelled
Set oRngEnd = Application.InputBox(Prompt:="Enter the range of Worksheet to inspect.", Type:=8)
'More Code
Exit Sub
Cancelled:
'Code if user presses Cancel
 

nickadeemus2002

New Member
Joined
Feb 18, 2004
Messages
41

ADVERTISEMENT

Andrew, I'll try your suggestion.

I'm on break from a two hour meeting (so far!)..
I have to get back in five minutes.

I'll see what happens.

Thanks for the suggestions guys!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
I tried enter the range, and it seemed to select the range for me without any problems.Be sure to enter the sheet name and range names without the quotation marks around them.

Here is the code I tried. It is simply the first part of your code, and I simply asked it to select the range I enter. See if it works for you.

Code:
Sub Copy2NewWorksheet()

Dim oOrgRng, oRng As Range
Dim oRngEnd As String
Dim oSheetName As String
Dim oNewRange, oNewBlankRange As Range
Dim oSht As Worksheet

'Prompt user for Sheet Name
oSheetName = InputBox("Enter Name of Worksheet to inspect.")

'Prompt user for Last Cell
 oRngEnd = InputBox("Enter the range of Worksheet to inspect.")

Set oOrgRng = Sheets(oSheetName).Range(oRngEnd)

oOrgRng.Select

End Sub
 

nickadeemus2002

New Member
Joined
Feb 18, 2004
Messages
41
JMiskey,

I copy and pasted your code as/is and it still didn't work. Is there a possibly some tools reference that I need to associate with the module? I figured if I just did a c/p it would work.
This is the line it still finds and error with:

Set oOrgRng = Sheets(oSheetName).Range(oRngEnd) o_O

chris
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
That often means that either:

1.) The sheet or range name you have entered is invalid. They must match EXACTLY, including any spaces, etc.

2.) The sheet or range you are trying to access is closed, hidden, or protected.

Could it be any of these things?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,563
Messages
5,765,119
Members
425,262
Latest member
sabry

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