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

[ranuse]

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.

[VBA] 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
[/VBA]

[shrivallabha]

Probably asking for a Object like:
[VBA]Dim temp as range
Set temp = Rows(1).Find(RequiredFields(i))
if temp.column = 0 then
[/VBA]

[GTO]

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]

Problem solved, thanks Mark.

[sujith1986]

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]

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