Find command not working as expected

[rex759]

Hello,

The code below looks at column A between A7:A25 for the first available blank cell. The column contains merged cells, A7 with A8, A9 and A10, etc… The worksheet is currently blank so when I run the macro, it should select A7.

For some reason, it always selects A9. If rows A9:A25 are not blank, then it selects A7.

Set foundBlank = Worksheets(objSheet.Name).Range("A7:A25").Find(What:="", lookat:=xlWhole)

I don’t understand why it doesn’t select A7 the first time. Any ideas? Here’s the complete code

Public Sub FindSheets()
Dim ws As Worksheet
Dim i As Integer
Dim objSheet As Worksheet
Dim foundBlank As Range

'save employee name and new rate
empNam = Application.InputBox(prompt:="Enter the Employee's last name to update their Pay Rate", Title:="Enter Laste Name")
emprte = Application.InputBox(prompt:="Enter their Pay Rate", Title:="Pay Rate (Example: 9.50)")
   
If empNam = False Or emprte = "" Then
       MsgBox ("You Pressed Cancelled!"), vbInformation, "OOPS!"
    Exit Sub

End If
  'Counter to add new people. Only ask question once; "Do you want to add a Employee"
    x = 1

'save Sheet code index number. Used for starting point
Wkshtnam = ActiveSheet.Index + 3


'this code loops through specific worksheets based on Index number through rest of the workbook
    For i = Wkshtnam To 56
       Set objSheet = FindSheetByName("Sheet" & i)
       Set oFound = Worksheets(objSheet.Name).Range("A7:A41").Find(What:="*" & empNam & "*", LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
   
   If oFound Is Nothing Then
         If x < 2 Then
             MsgBox (empNam & " is not listed for week ending ") & objSheet.Name
            quest = MsgBox("Add Employee to the rest of the year?", vbYesNo + vbQuestion, "Add Employee?")
        
            'if user replys "no", exit sub
             If quest = vbNo Then
                MsgBox ("Cancelled!")
                Exit Sub
             End If
             
            'add new employee
            Dim c As Range
            empnam2 = Application.InputBox(prompt:="Retype Employee's First and Last name", Title:="First and Last Name")
            empwith = Application.InputBox(prompt:="What is the withholding value? Example:0, 1, 2...")
    
    End If
          If x < 2 Then
            'Adding Cook or Wait Staff
               quest2 = MsgBox("Is this a Cook? Answer No if adding Wait Staff", vbYesNoCancel + vbQuestion, "COOK")
            End If
            If quest2 = vbCancel Then
                MsgBox ("Cancelled!")
                Exit Sub
            End If
            'if user replys "no", add to wait staff range
                        If quest2 = vbNo Then
               Set foundBlank = Worksheets(objSheet.Name).Range("A7:A25").Find(What:="", lookat:=xlWhole)
                
               Else:
                 Set foundBlank = Worksheets(objSheet.Name).Range("A27:A41").Find(What:="", lookat:=xlWhole)
               End If
                      
             'add new employee
             foundBlank = empnam2
        
            'add new employee rate
             NewEmp2 = Worksheets(objSheet.Name).Cells.Range("A" & foundBlank.Row).Address
             Worksheets(objSheet.Name).Range(NewEmp2).Offset(0, 10).Value = emprte
             Worksheets(objSheet.Name).Range(NewEmp2).Offset(0, 20).Value = empwith
    
    x = x + 1
    
    Else:
      
      ttt = Cells.Range("A" & oFound.Row).Address
      Worksheets(objSheet.Name).Range(ttt).Offset(0, 10).Value = emprte
      MsgBox (empNam & " found on worksheet ") & objSheet.Name
   End If
      
    Next
    MsgBox ("Complete")
End Sub

Function FindSheetByName(ByVal v_strCodeName As String) As Worksheet
    Dim objSheet As Worksheet

    Set FindSheetByName = Nothing

    For Each objSheet In ActiveWorkbook.Sheets
        If objSheet.CodeName = v_strCodeName Then
            Set FindSheetByName = objSheet
            Exit Function
        End If
    
    Next
End Function

 

[6StringJazzer]

Find starts after the first cell in the range, so the first cell it checks is A8. Add the following:

Set FoundBlank = Range("A7:A25").Find(What:="", [COLOR=#ff0000]after:=Range("A25"), [/COLOR]lookat:=xlWhole)

Also, merged cells can cause a lot of headaches, especially if you are using VBA. I would recommend against it.

 

[rex759]

Find starts after the first cell in the range, so the first cell it checks is A8. Add the following:

Set FoundBlank = Range("A7:A25").Find(What:="", [COLOR=#ff0000]after:=Range("A25"), [/COLOR]lookat:=xlWhole)

Also, merged cells can cause a lot of headaches, especially if you are using VBA. I would recommend against it.

The merged cells has been challenging. Your edit works perfectly for me. Thank you