Run Time Error 91, object variable not set??

[nickadeemus2002]

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
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

 

[Joe4]

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]

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]

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]

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

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]

JMiskey,

I tried both and neither worked

 

[nickadeemus2002]

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]

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.

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]

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)

chris

 

[Joe4]

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?