Menu
Module frmWelcome.RefreshForm. Symptoms Upon launching and logging into the software, error 91 is raised. Cause A The current AIUEvent database is not available. Jul 20, 2015 Make sure you aren't referring to an object variable that has been set to Nothing. Search your code for the keyword Nothing, and revise your code so that the object isn't set to Nothing until after you have referenced it. Make sure that any array variables are dimensioned before you access them.
Hello,
I am new to VBA editing, and I have written a code that is giving 'Run-time error '91': Object variable or With block variable not set'. The code is designed to copy any rows with a value in Column F, and paste them in another worksheet.
I am wondering if, in the event there are no values at all in Column F, the code produces an error. The code is below. I have marked the line that 'Debug' leads me to in bold below.
Sub Transfer()
'This macro copy-pastes rows from the Data Collection tab into the Data Storage tab, then deletes duplicates
'Find rows that contain any value in column F and copy them
Dim cell As Range
Dim selectRange As Range
Dim selectRange As Range
On Error GoTo Errorcatch
Sheets('Data Collection').Select
For Each cell In ActiveSheet.Range('F:F')
If (cell.Value <> ') Then
If selectRange Is Nothing Then
Set selectRange = cell
Else
Set selectRange = Union(cell, selectRange)
End If
End If
Next cell
If (cell.Value <> ') Then
If selectRange Is Nothing Then
Set selectRange = cell
Else
Set selectRange = Union(cell, selectRange)
End If
End If
Next cell
selectRange.EntireRow.Select
selectRange.EntireRow.Copy
'Paste copied selection to the worksheet 'Data Storage' on the next blank row
Sheets('Data Storage').Range('A65536').End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues
Sheets('Data Storage').Select
ActiveSheet.Range('A1').Select
Exit Sub
Errorcatch:
MsgBox Err.Description
End Sub
Sheets('Data Storage').Range('A65536').End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues
Sheets('Data Storage').Select
ActiveSheet.Range('A1').Select
Exit Sub
Errorcatch:
MsgBox Err.Description
End Sub
Any ideas?
Maybe 'selectRange' has no values and Excel doesn't know which row to select?
Thanks,
Parker
-->There are two steps to creating an object variable. First you must declare the object variable. Then you must assign a valid reference to the object variable using the Set statement.
Similarly, a With...End With block must be initialized by executing the With statement entry point. This error has the following causes and solutions:
- You attempted to use an object variable that isn't yet referencing a valid object.Specify or respecify a reference for the object variable. For example, if the Set statement is omitted in the following code, an error would be generated on the reference to MyObject:
- You attempted to use an object variable that has been set to Nothing.Respecify a reference for the object variable. For example, use a new Set statement to set a new reference to the object.
- The object is a valid object, but it wasn't set because the object library in which it is described hasn't been selected in the Add References dialog box.Select the object library in the Add References dialog box.
- The target of a GoTo statement is inside a With block.Don't jump into a With block. Make sure the block is initialized by executing the With statement entry point.
- You specified a line inside a With block when you chose the Set Next Statement command.The With block must be initialized by executing the With statement.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
Note
Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.