PDA

View Full Version : Solved: Error 91, Object Variable or with block not set



ranuse
02-21-2011, 09:58 PM
Hi,

I'm having trouble with the error described in the title, error 91. My code is trying to find a string in the first row of the worksheet and return the column number. I expect that if it does not find anything, the function returns 0. When I run the code on a blank worksheet, I receieved error 91. In the debug mode, temp is shown to equal 0....so I'm not sure what the problem is.

Any advices is appreciated.

Dim i As Integer
Dim temp As Integer
Dim RequiredFields As Variant
RequiredFields = Array("Failed", "Not Executed", "Passed")

For i = 0 To 2

temp = Rows(1).Find(RequiredFields(i)).Column

If temp = 0 Then
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
ActiveCell.FormulaR1C1 = RequiredFields(i)
End If
Next i

shrivallabha
02-21-2011, 10:54 PM
Probably asking for a Object like:
Dim temp as range
Set temp = Rows(1).Find(RequiredFields(i))
if temp.column = 0 then

GTO
02-22-2011, 12:23 AM
Hi there,

"Object variable or With block variable not set" means in this case, that the code falls over trying to return a column number for a range that does not exist. That is, the .Find did not return a range, thus, returning a Column isn't happening.

First attempt to set a reference to the return of .Find. Then test to see if the range is something (exists).

Option Explicit

Sub exa()
Dim i As Integer
Dim temp As Integer
Dim RequiredFields As Variant
RequiredFields = Array("Failed", "Not Executed", "Passed")

Dim myRange As Range

For i = 0 To 2
Set myRange = Rows(1).Find(RequiredFields(i))

If myRange Is Nothing Then

Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Value = RequiredFields(i)
End If
Next i
End Sub
Notice that we can skip the selecting and Selection as well.

Hope that helps,

Mark

ranuse
02-22-2011, 12:07 PM
Problem solved, thanks Mark.

sujith1986
01-31-2013, 04:34 AM
How to get rid of this error? I am not getting this error while using a macro, but one of my colleague gets this. We both use Windows platform and a Dell system, so ideally it should not come... Anyone can help?

GTO
01-31-2013, 05:57 AM
"Something" doesn't exist on your partner's PC that does exist on yours. A 'cut-down' (simplified/basic) bit of the file that works on yours, but not on your partner's PC would most likely be helpful (.xls format please).