CODEWITHSUNDEEP
vbaexcel
Christopher Job
Christopher Job

Reputation: 1

VBA .Find() function not finding excell cell

I'm using the .Find function in workbook 1 to search for a number in a worksheet in workbook 2 and set it to a range object called "found".

I'm getting Run-time error '91': Object variable or With block variable not set. I know for a fact that the number exists in that second workbook, but I guess doesn't want to be found. An interesting bug is that when I run this for the first time it will work fine. But if I close workbook 1 and open it again, then the .Find function can't find the number anymore.

Can anyone see an apparent problem in my code below?

Dim found As Range
Dim ecrNum As String

Set wb = ThisWorkbook
ecrNum = ThisWorkbook.ActiveSheet.Range("H5")

'Opens ECR Log workbook
Set ECRlog = Workbooks.Open("filepath")
ECRlog.ActiveSheet.Unprotect ("x")

'Resets Filters in the ECR Log
If ECRlog.ActiveSheet.FilterMode Then
ECRlog.ActiveSheet.AutoFilter.ShowAllData
End If

'Searches the ECR log for the specified ECR #
Set found = ECRlog.ActiveSheet.Range("C:C").Find(What:=ecrNum, _
            LookIn:=xlValues, LookAt:=xlWhole)

'Pushes info from ECR form to ECR Log
ECRlog.ActiveSheet.Cells(found.Row, 5) = proj
ECRlog.ActiveSheet.Cells(found.Row, 6) = model
ECRlog.ActiveSheet.Cells(found.Row, 7) = ecn
ECRlog.ActiveSheet.Cells(found.Row, 9) = dueDate

Upvotes: 0

Views: 151

Answers (2)

Emilio Lucas Ceroleni
Emilio Lucas Ceroleni

Reputation: 1608

Worksheet.Range returns a Range object that represents a cell or a range of cells. Taking into consideration Set found = ECRlog.ActiveSheet.Range("C:C").Find(What:=ecrNum, _ LookIn:=**xlValues**, LookAt:=xlWhole) I believe you should try ecrNum = ThisWorkbook.ActiveSheet.Range("H5").Value instead of your fifth line of code. More on Worksheet.Range property here

Upvotes: 0

Christopher Job
Christopher Job

Reputation: 1

So I've been troubleshooting and testing this problem endlessly and believe I've identified the cause. The solution is trickier to identify (for me), but there is an easy workaround.

When workbook 1 runs the program but workbook 2 is already open, that's when I get the error. When I close workbook 2 and run the program again, I don't get an error, and everything runs fine. So there must be a read/write issue that is preventing the .find() command from successfully finding the number it's looking for.

Upvotes: 0

Related Questions