Thread: cells.Find not working

No, I removed the protection from one of them just to be sure.
I also tried Sheets("Prime Recipient").Range("B33").Value = FTE

but nothing works.

Just wanted to confirm whether
Sheets("Prime Recipient").Range("B33").Value = FTE

is a valid command.

FTE is a variable that gets some value through the earlier part of the code. I can see in the watch window that the code is assigning correct values to this variables.

Essentially just updating the cell with this value is not working.

I have unprotected the entire sheet. Besides, even originally, these cells are input cells which means user has to enter text or numbers here.

Here is some useful amount of code :

With Application.FileSearch
        .LookIn = Directory
        .FileType = msoFileTypeExcelWorkbooks
        .Filename = code

         If .Execute > 0 Then
            
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & ".xls")
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "s.xls")
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "Contr.xls")
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "sContr.xls")
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "-DCRI.xls")
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "-DCRIs.xls")
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "SubOnly.xls")
                
                
                               
                mybookname.Sheets("Prime Recipient").Select
                mybookname.Sheets("Prime Recipient").Activate
                
                Y = ActiveWorkbook.name
                                  
                If Right(Y, 10) = "sContr.xls" Or Right(Y, 9) = "Contr.xls" Then
                    
                    ActiveSheet.Range("F31").Select
                    ActiveCell.Value = FTE
                    
                    ActiveSheet.Range("B33").Select
                    ActiveCell.Value = JobString
                    ActiveWorkbook.Close savechanges:=True
                    
                    Application.Goto Reference:="Bookmark"
                    ActiveCell.Offset(blankcount, 0).Select
                    Pointer = ActiveCell.Value
                    
                Else                        
                                                
                        If Right (Y, 11) <> "SubOnly.xls" Then                        
                           
                            Sheets("Prime Recipient").Range("b33").Value = FTE
                            
                            Sheets("Prime Recipient").Range("d33").Value = JobString
                            
                            ActiveWorkbook.Close savechanges:=True
                            
                            Application.Goto Reference:="Bookmark"
                            ActiveCell.Offset(blankcount, 0).Select
                            Pointer = ActiveCell.Value
                        
                        Else
                        
                            ActiveWorkbook.Close savechanges:=True
                            Application.Goto Reference:="Bookmark"
                            ActiveCell.Offset(blankcount, 0).Select
                            Pointer = ActiveCell.Value
                        
                        End If
                
                End If
                                
        Else
    
            Sheets("ERROR").Activate
            Range("A1").Activate
            
            ActiveCell.Offset(cntr, 0).Select
            ActiveCell.Value = code
            ActiveCell.Offset(0, 1).Value = "Code exists in table but file doesnt exist"
            cntr = cntr + 1
            
            Application.Goto Reference:="Bookmark"
            ActiveCell.Offset(blankcount, 0).Select
            
            Pointer = ActiveCell.Value
    
        End If
        
    End With

Certain parts of the code pertain to logic in the earlier part of the program and can be overlooked for this discussion e.g.

Application.Goto Reference:="Bookmark"
                            ActiveCell.Offset(blankcount, 0).Select
                            Pointer = ActiveCell.Value

There are multiple file types and each code could have any file type. So code is appended to the file type. E.g

2010q3-203.xls is one valid file, similarly
2010q3-303s.xls is another valid file.

Moreover, there will always be only 1 file associated with a code. So what I am doing is trying a code with each file type possible. If there is any error I have the

On Error Resume Next

given at the start of the program. This answers your first question.

Coming to the second one. Whichever workbook gets opened, I am not sure which worksheet gets activated, I have to ensure that the worksheet "Prime Recipient" is the one that gets activated. It is better to be safe. That was my thought for including :

mybookname.Sheets("Prime Recipient").Select
mybookname.Sheets("Prime Recipient").Activate

Finally, I am assigning the value of myworkbookname to Y just to use it in the following IF statement. I could do away with it, but I dont see that to be a problem or issue.

Currently I want to focus on the update part. Once that is resolved, I can tidy up the code.

Thanks

Regarding the IF statement and "first test is subsumed by the second".

Thanks for pointing it out, I will incorporate it.

I think I can just keep the second part.

If Right(Y, 9) = "Contr.xls" Then

Thanks

This might be surprising.

I changed the code as follows:

With Application.FileSearch
        .LookIn = Directory
        .FileType = msoFileTypeExcelWorkbooks
        .Filename = code

         If .Execute > 0 Then
            
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & ".xls")
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "s.xls")
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "Contr.xls")
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "sContr.xls")
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "-DCRI.xls")
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "-DCRIs.xls")
                Set mybookname = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "SubOnly.xls")
                
                
                               
                mybookname.Sheets("Prime Recipient").Select
                mybookname.Sheets("Prime Recipient").Activate
                
                ' Worksheets("Prime Recipient").EnableSelection = xlNoRestrictions
                
                Y = ActiveWorkbook.name
                                  
                If Right(Y, 11) <> "SubOnly.xls" Then
                        
                            Cells.Find(What:="Number of Jobs*", LookIn:=xlValues, MatchCase:=False).Activate
                            ActiveCell.Offset(1, 0).Value = FTE
                            
                            Cells.Find(What:="Description of Jobs Created*", LookIn:=xlValues, MatchCase:=False).Activate
                            ActiveCell.Offset(1, 0).Value = JobString
                            
                            ActiveWorkbook.Close savechanges:=True
                            
                            Application.Goto Reference:="Bookmark"
                            ActiveCell.Offset(blankcount, 0).Select
                            Pointer = ActiveCell.Value
                        
                Else
                        
                            ActiveWorkbook.Close savechanges:=True
                            Application.Goto Reference:="Bookmark"
                            ActiveCell.Offset(blankcount, 0).Select
                            Pointer = ActiveCell.Value
                        
                End If
                
                                
        Else
    
            Sheets("ERROR").Activate
            Range("A1").Activate
            
            ActiveCell.Offset(cntr, 0).Select
            ActiveCell.Value = code
            ActiveCell.Offset(0, 1).Value = "Code exists in table but file doesnt exist"
            cntr = cntr + 1
            
            Application.Goto Reference:="Bookmark"
            ActiveCell.Offset(blankcount, 0).Select
            
            Pointer = ActiveCell.Value
    
        End If
        
    End With

Essentially, first I manually edited the EnableSelection option of the worksheet to xlNoRestrictions and checked whether cells.find is working. It did work.

So I thought of putting it in the code as

Worksheets("Prime Recipient").EnableSelection = xlNoRestrictions

Then things were working the way it should. So I just wanted to confirm whether it was this change itself that caused the program to work properly. Hence I removed all the files, brought in new ones from backup. In these files I had not altered anything manually. Then I commented the

Worksheets("Prime Recipient").EnableSelection = xlNoRestrictions

.

Even then the program was working and behaving normally. Now my concern is what if the next time, during actual run, the cells.find misbehaves. Any guesses ?

Thanks

Just to elaborate and emphasize,

after commenting the

'   Worksheets("Prime Recipient").EnableSelection = xlNoRestrictions

it appears that I have made no changes and suddenly the cells.find started working.

This is what is not clear to me.

Thanks