title: VBA EXCEL Run time error 1004 Method 'Range of Object_worksheet' failed
body: <p>I'm trying to extract data from worksheet after selecting an option in the combobox. So what I'm doing here is that when I click the search button, based on the option chosen in the SearchSelectPPComboBox, I should go into the ws in the wb to find this option value then locate which row this value is in so I can extract the data individually by moving to the next column in the same row. </p>
  
  <p>However, my code below is having "Run time error 1004 Method 'Range of Object_worksheet' failed" at this line </p>
  
  <p><strong>Set FoundCell = ws.Range("F8:F").Find(what:=WHAT_TO_FIND, lookat:=xlWhole)</strong></p>
  
  <p>Thank you for your help in advance!!</p>
  
  <pre><code>Private Sub SearchButton_Click()
      If SearchTeamComboBox.ListIndex &lt; 0 And SearchSelectPPComboBox.ListIndex &lt; 0 Then
      MsgBox "Please select Team and the Process/project you want to search ."
      SearchTeamComboBox.SetFocus
      ElseIf SearchTeamComboBox.ListIndex &lt; 0 Then
      MsgBox "Please select Team."
      SearchTeamComboBox.SetFocus
      ElseIf SearchSelectPPComboBox.ListIndex &lt; 0 Then
      MsgBox "Please select the Process/project you want to search ."
      SearchSelectPPComboBox.SetFocus
      Else
  
      Const WHAT_TO_FIND As String = "SearchSelectPPComboBox.value"
      Dim ws As Excel.Worksheet
      Dim FoundCell As Excel.Range
  
      Set ws = Sheets(SearchTeamComboBox.Value)
      Set FoundCell = ws.Range("F8:F").Find(what:=WHAT_TO_FIND, lookat:=xlWhole)
      If Not FoundCell Is Nothing Then
          MsgBox (WHAT_TO_FIND &amp; " is found ")
          Me.checklistComboBox.Value = FoundCell.Offset(0, 1).Value
  
      End If
      End If
      End Sub
  </code></pre>
  
  <p>Additional codes: </p>
  
  <pre><code>Private Sub SearchTeamComboBox_Change()
      Application.EnableEvents = False
      SearchSelectPPComboBox.Clear
      Application.EnableEvents = True
  
  Dim PP As Object
  Dim rngList As Range
  Dim strSelected As String
  Dim LastRow As Long
  
          ' check that a team has been selected
        If SearchTeamComboBox.ListIndex &lt;&gt; -1 Then
  
             strSelected = SearchTeamComboBox.Value
              If strSelected = "ACLT" Then
              LastRow = Worksheets("ACLT").Range("E" &amp; Rows.Count).End(xlUp).row
              Set rngList = Worksheets("ACLT").Range("E8:E" &amp; LastRow)
  
                  ElseIf strSelected = "AIF/CIF" Then
                  LastRow = Worksheets("AIFCIF").Range("E" &amp; Rows.Count).End(xlUp).row
                  Set rngList = Worksheets("AIFCIF").Range("E8:E" &amp; LastRow)
  
                  ElseIf strSelected = "FDM" Then
                  LastRow = Worksheets("FDM").Range("E" &amp; Rows.Count).End(xlUp).row
                  Set rngList = Worksheets("FDM").Range("E8:E" &amp; LastRow)
  
                  ElseIf strSelected = "Imaging" Then
                  LastRow = Worksheets("Imaging").Range("E" &amp; Rows.Count).End(xlUp).row
                  Set rngList = Worksheets("Imaging").Range("E8:E" &amp; LastRow)
  
                  ElseIf strSelected = "MRT" Then
                  LastRow = Worksheets("MRT").Range("E" &amp; Rows.Count).End(xlUp).row
                  Set rngList = Worksheets("MRT").Range("E8:E" &amp; LastRow)
  
                  ElseIf strSelected = "PAT" Then
                  LastRow = Worksheets("PAT").Range("E" &amp; Rows.Count).End(xlUp).row
                  Set rngList = Worksheets("PAT").Range("E8:E" &amp; LastRow)
  
                  ElseIf strSelected = "SSU" Then
                  LastRow = Worksheets("SSU").Range("E" &amp; Rows.Count).End(xlUp).row
                  Set rngList = Worksheets("SSU").Range("E8:E" &amp; LastRow)
  
                  ElseIf strSelected = "VEL" Then
                  LastRow = Worksheets("VEL").Range("E" &amp; Rows.Count).End(xlUp).row
                  Set rngList = Worksheets("VEL").Range("E8:E" &amp; LastRow)
  
              End If
                  For Each PP In rngList
                     SearchSelectPPComboBox.AddItem PP.Offset(, 1)
              Next PP
  
      End If
  
  End Sub
  </code></pre>