Hi, I’m working with a macro that finds new entities using an embedded VLOOKUP in an ISERROR function. It assigns “TRUE” or “FALSE”, and then it sorts the results and uses Selection.Find to activate the cell corresponding to the first new entity. From there, it automatically populates my main list with the new entities. The problem is that sometimes there are no new entities, and I get the following. “Run-time error ‘91’: Object variable or With block variable not set.” The search code I’m using is: Selection.Find(What:=“TRUE”, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate I am new to using VBA, but I am hoping that there is an easy fix. If you know your way around this problem, I would very much appreciate your help.
I have to say that this code is not very robust and is not the way I’d recommend doing anything … but, I built a small spreadsheet to see whether I could replicate your error. On Sheet1, in cell A1, I put the value FALSE, then some random TRUE, FALSE, blanks and so on to fill out a few more rows in column 1. Then, I wrote a test subroutine: Sub test() Sheets(“sheet1”).Select Range(“a1:a10”).Select Selection.Find(What:=“TRUE”, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate End Sub I can replicate your error when there are no values of TRUE in any cell in the selected range. If I put TRUE in any cell of A1:A10, the code works. So, what is happening is that there is no value of TRUE in any of the selected cells Whoever wrote this needs to have more robust error handling so the user isn’t presented with a message like this. If you can’t get them to resolve it, interpret the error as meaning that there are no values of “TRUE” in the selected range.
For any Excel VBA problem use ‘Google Groups’, it is invaluable and has helped me a lot over the years.
I appreciate the help. I’ve been looking around on Google Groups and the website: http://www.ozgrid.com/VBA/find-method.htm, and I think I’m just going to have to learn more about VBA before I can really understand how to get around this error. For the time being, I’ve got it set up as a 2 macros with one manual copy/paste between them, which is ok. Thanks again.
Why don’t you just add a While loop so it only runs when there is a TRUE value in your selection?
Here’s what I need it to do: When there’s a “TRUE” value, I need it to Copy and Paste the corresponding rows into another sheet, and then continue on to the next part of the macro. I can do this, no problem. But, to get the rows that contain “TRUE” values, the only way I know is to use the Selection.Find code detailed above. So, on days when every value is “FALSE”, I simply need Excel to ignore that, and continue on to the next part of the macro. Basically, I need a way to tell Excel to move on to the next part of the macro if the Find function returns no results. Will the While Loop prevent Excel from interrupting the macro when the Selection.Find function returns zero “TRUE” results?
I’m assuming column A contains TRUE/FALSE values you’re looking for Sub Test() Dim count As Integer count = Application.CountIf(Columns(“A:A”), “TRUE”) If (count > 1) Then 'this will mean more than 1 TRUE. Else 'this will mean that there are no TRUE’s and hence your exception. End If End Sub Hope that helps!!
This works perfectly. Thank you!
wow… two mins between me posting up a solution and you testing AND responding… you have AF on auto-refresh???
Just good timing. This helps out a ton. Thanks again.