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

Question

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.

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 — AmatureMarco
– AmatureMarco, Commented Jan 8, 2018 at 11:26
I clearly haven't got the hang of the formatting on this forum.. but that's the code — AmatureMarco
– AmatureMarco, Commented Jan 8, 2018 at 11:27
Please, don't post relevant info in the comments, edit your question and add the info there. — Magnetron
– Magnetron, Commented Jan 8, 2018 at 11:29

Vityata · Accepted Answer · 2018-01-08 11:43:01Z

0

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.

edited Jan 8, 2018 at 11:43

answered Jan 8, 2018 at 11:29

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AmatureMarco Over a year ago

Hi, thanks for your help. But this just returns an "Object doesn't support this property or method" error

Vityata Over a year ago

@AmatureMarco - did you try only my code or have you tried to embed it in your code? The 5 lines I wrote should be working quite ok.

AmatureMarco Over a year ago

I assumed that it would work and went straight to implementing it into my own code and that's when I get the error.

AmatureMarco Over a year ago

I have found that using this method only work with the .range in the code and spits out the error if I don't. But I have otherwise got it working all thanks to you.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related