Find value w/VB creates error, ignoring "On Error Resume Next"

  • I have a bit of VBA that I've used on multiple occasions. I look for a row of data in a table, and if it's not there, I add it. If it is, I overwrite the data. Here's how I do it:



    I've used similar code before with great success, but for some reason I'm now getting "Run-time error '91': Object variable or With block variable not set" and it's the DataFound = Columns(1).Find... line that is causing the error. I expect it to error... the data isn't there, right? But that's why I have the line of code "On Error Resume Next" there. Very confused as to why that isn't happening.


    Any ideas?

  • Re: Find value w/VB creates error, ignoring "On Error Resume Next"


    Why not like this?


    Code
    1. If Columns(1).Find(What:=DateTime, After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) Is Nothing Then
    2. InputRow = Sheet7.Range("A" & Rows.Count).End(xlUp).Row
    3. Else
    4. InputRow = Columns(1).Find(What:=DateTime, After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row - 1
    5. End If


    I couldn't try if it works, but think it should :-)


    Please remember to say THANKS! if it helps you (-:


    (oh and originally it might error cause you wrote "on error resume next" with two o's in "on"...)

  • Re: Find value w/VB creates error, ignoring "On Error Resume Next"


    Your code is working flawlessly. Much cleaner, too - thanks very much. I learned something new today!


    (Re: two o's, I somehow deleted the hard return from one line to the next when I pasted the code. I edited the original post to display it as it was in my code)

  • Re: Find value w/VB creates error, ignoring "On Error Resume Next"


    When testing for an error remove the on error statement.


    In fact I would not use that error statement at all