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

 

[nickadeemus2002]

JMiskey,

You are a genius! It was the cell protection that was throwing it all off. Thanks for bearing with me here. But............

Here's another challenge.

The procedure you helped me with gives me a sheet with a bunch of blue squares where the blanks are located. Is there a way to make the Unique ID number (integer) of the first column ("A") show up on the second sheet in the first column with their corresponding placement in relation to sheet 1?

I am working on that right now, but our meeting is about to begin again. suggestions?

chris

 

[Joe4]

I hadn't really looked at what your code is doing, just the syntax of it.

Can you explain exactly what you are trying to do with your code, and post some sample data (maybe using "Colo's HTML Maker utility for displaying your Excel Worksheet on the board", which can be found at the bottom of every page on this forum?

I will be leaving shortly, so may not get a chance to look at it until tomorrow.

 

[nickadeemus2002]

JMiskey,

Let me give you a little background about myself. I work for a local nonprofit organization that helps people with disabilities live more independent lives. I just won a grant for $280K and need to develop a compliance and reporting system. Problem is, we don't have money to buy expensive software packages, so I have to use whatever's around. The most powerful tools I have are the Microsoft Office Products and VBA.

The workbook I have imports a data table from access into excel. I wrote that yesterday and thank God it works. Next I have to find the blank cells which are within the data to determine if there is any missing information. Thanks to you, I am able to do that with blue cells!!. The procedure you helped me with checks the imported table for blanks, copies those blanks onto a new worksheets and makes those blanks blue cells. Why blue cells? Well, my boss, the Executive Director, is visually impaired and she wants to see the end product's functionality. With blue cells, she can get an idea of how accurate people are filling in the access database. Now, what I needed was to give here some sense of perspective of where these errors are occurring relative to the record # and field. The first column is always the Unique ID (integer) and the field headers are always on row 1. Right now the Located Blanks worksheet is just a bunch of blue cells where the blanks were in the selected range area. I wanted to paste all the Unique ID's all the way down column A and paste all the field headers along row 1 so she can have an idea of which records are missing info and which field is missed.

I was trying to use Colo's from below, but I can't override the security settings on our workstations. Can I send the workbook to you somewhere else?


Thanks for all your help

ChrisV

PS

Here's the code I have so far thanks to you!

'This routine will find the blank cells in a
'worksheet and highlight them in a new worksheet.
'
Public Sub BlankChecker()
'Declare all your variables
Dim oOrgRng, oRng As Range 'range properties
Dim oRngEnd As String 'will be input by user
Dim oSheetName As String 'will be input by user
Dim oNewRange, oNewBlankRange As Range 'new range properties
Dim oSht As Worksheet 'new worksheet with blanks

'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.")

'Construct the area to be checked
Set oOrgRng = Sheets(oSheetName).Range(oRngEnd)

'Now select it
oOrgRng.Select

'Make sure you get the blank cells
Set oRng = oOrgRng.SpecialCells(xlCellTypeBlanks)

'if there are no blank cells found, exit this sub directly


'If there are blank cells, here's the branching logic
If oRng.Count > 0 Then

'Create one new sheet
Set oSht = Sheets.Add

'Name new sheet Blanks;
oSht.Name = "Located Blanks"

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

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

'Select the newly created blanks sheet
Sheets("Located Blanks").Select

'Select the cell origin for 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)

'The contents from the source range werew copied to the
'new sheet. Now do some housecleaning and clear up your mess
oNewRange.ClearContents

End If

End Sub

 

[Joe4]

How about if we change it so instead of just copying the cell with the blank, we copy the whole row that it is in? In order to do this, change this section of your code:

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

to this

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

If you don't want to copy the whole row, please indicate where the unique identifier falls in relation to the column you are checking for blanks, and we will try some different code.

 

[nickadeemus2002]

Good Morning JMiskey. Hope all is well.


I made the change you advised. I get a new runtime error in this block of code:

'Select the cell origin for paste
Call Range("A1").PasteSpecial(xlPasteAll)
Set oNewRange = Selection

Set oNewBlankRange = oNewRange.SpecialCells(xlCellTypeBlanks) :hammer: 

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

It's a runtime error 1004, "no cells were found." There's on last meeting this morning so I should be back in 30 minutes. And like always, thanks for your help.

chris

 

[just_jon]

? Call Range("A1").PasteSpecial(xlPasteAll)

should be just Range... ?

 

[WendyRussell858Ba]

Reinstall the Software/Application


Incomplete installation of a software/application may also result in “VBA runtime error 91.” Also, reinstallation of the software may also result in “VBA runtime error 91,” this is due to remnant files, DLL, and registry of the previously installed software. To fix this is to uninstall the software and run cleaner to delete remnant and temporary files.
Check this URL
https://www.errorsolutions.tech/error/vba-runtime-error-91/