CODEWITHSUNDEEP
vbaexcel
AmatureMarco
AmatureMarco

Reputation: 29

VBA for excel gives either "object variable not set" or "object required"

I've been trying to search a table for a predetermined value and if that value is not there, add that value and then create a new line in the table.

Sounds simple I know.

tblrow = tbllook.Range.Columns(1).Cells.Find(shtfind, searchorder:=xlByRows, searchdirection:=xlPrevious).Row 

MsgBox tblrow 

If tblrow Is Nothing Then 
    tblcount = tbllook.Range.Rows.Count 
    tbllook.Range(tblcount, 1).Select 
    ActiveCell = shtfind 
    tbllook.ListRows.Add 
End If

Tblerow is currently Dim'd as variant.

I get the a correct answer back in the msgbox when it is found in the table but then spits out an "Object Required" error on the If statement.

But when the value is not found in the table it still spits out the same error.

I have tried changing my Dim of tblrow to an Object but then get an "object variable or With block variable not set" on my .find line even I put set in front of it.

My googling of the problem has told me that is because it didn't find a value in the table and that the If statement that I have added should sort this but it has not.

Please help.

Upvotes: 0

Views: 272

Answers (1)

Vityata
Vityata

Reputation: 43565

Try to check whether the "found" value Is Nothing:

Sub TestMe()    
    If tbllook.Columns(1).Cells.Find("test") Is Nothing Then
        MsgBox "It is Nothing?"
    End If    
End Sub

Then it would work. Furthermore, the tbllook.Range would give an error the way you are using it.

And congrats for adding the code to your question. As a next step, try copying and pasting the code, as suggested in the comments. How do I format my code blocks? You would get more attention to the question this way.

Upvotes: 0

Related Questions