VBA EXCEL Run time error 1004 Method 'Range of Object_worksheet' failed

Question

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.

However, my code below is having "Run time error 1004 Method 'Range of Object_worksheet' failed" at this line

Set FoundCell = ws.Range("F8:F").Find(what:=WHAT_TO_FIND, lookat:=xlWhole)

Thank you for your help in advance!!

Private Sub SearchButton_Click()
    If SearchTeamComboBox.ListIndex < 0 And SearchSelectPPComboBox.ListIndex < 0 Then
    MsgBox "Please select Team and the Process/project you want to search ."
    SearchTeamComboBox.SetFocus
    ElseIf SearchTeamComboBox.ListIndex < 0 Then
    MsgBox "Please select Team."
    SearchTeamComboBox.SetFocus
    ElseIf SearchSelectPPComboBox.ListIndex < 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 & " is found ")
        Me.checklistComboBox.Value = FoundCell.Offset(0, 1).Value

    End If
    End If
    End Sub

Additional codes:

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 <> -1 Then

           strSelected = SearchTeamComboBox.Value
            If strSelected = "ACLT" Then
            LastRow = Worksheets("ACLT").Range("E" & Rows.Count).End(xlUp).row
            Set rngList = Worksheets("ACLT").Range("E8:E" & LastRow)

                ElseIf strSelected = "AIF/CIF" Then
                LastRow = Worksheets("AIFCIF").Range("E" & Rows.Count).End(xlUp).row
                Set rngList = Worksheets("AIFCIF").Range("E8:E" & LastRow)

                ElseIf strSelected = "FDM" Then
                LastRow = Worksheets("FDM").Range("E" & Rows.Count).End(xlUp).row
                Set rngList = Worksheets("FDM").Range("E8:E" & LastRow)

                ElseIf strSelected = "Imaging" Then
                LastRow = Worksheets("Imaging").Range("E" & Rows.Count).End(xlUp).row
                Set rngList = Worksheets("Imaging").Range("E8:E" & LastRow)

                ElseIf strSelected = "MRT" Then
                LastRow = Worksheets("MRT").Range("E" & Rows.Count).End(xlUp).row
                Set rngList = Worksheets("MRT").Range("E8:E" & LastRow)

                ElseIf strSelected = "PAT" Then
                LastRow = Worksheets("PAT").Range("E" & Rows.Count).End(xlUp).row
                Set rngList = Worksheets("PAT").Range("E8:E" & LastRow)

                ElseIf strSelected = "SSU" Then
                LastRow = Worksheets("SSU").Range("E" & Rows.Count).End(xlUp).row
                Set rngList = Worksheets("SSU").Range("E8:E" & LastRow)

                ElseIf strSelected = "VEL" Then
                LastRow = Worksheets("VEL").Range("E" & Rows.Count).End(xlUp).row
                Set rngList = Worksheets("VEL").Range("E8:E" & LastRow)

            End If
                For Each PP In rngList
                   SearchSelectPPComboBox.AddItem PP.Offset(, 1)
            Next PP

    End If

End Sub

Answer 1

Nicolas provided the anwser to your first issue.

Whole Column. Now there's no error, but right now nothing happened when I click search. I'm trying to get the data in the column beside the found cell into a textbox in my userform. @nicolas– Gwen44 mins ago

The following code is incorrect:

Const WHAT_TO_FIND As String = "SearchSelectPPComboBox.value"

You are giving the value "SearchSelectPPComboBox.value" to WHAT_TO_FIND. The code should be:

Const WHAT_TO_FIND As String = SearchSelectPPComboBox.value

the text inside "" becomes a string thus its searching for the string "SearchSelectPPComboBox.value", by removing the "" the value of SearchSelectPPComboBox.value becomes the string.

EDIT:

a constant requires a non-variable string. SearchSelectPPComboBox.value is a variable string and therefor can not be defined as a constant.

Use the following code instead:

Dim WHAT_TO_FIND as string 
WHAT_TO_FIND = SearchSelectPPComboBox.value

Answer 2

It should be, because you used incomplete range as Range("F8:F").

Where you want to find the word, the whole F column or just the part of F column.

For the whole column, you should use as follow:

Set FoundCell = ws.Range("F:F").Find(what:=WHAT_TO_FIND, lookat:=xlWhole)

For the part of column, you should use as follow:

Set FoundCell = ws.Range("F8:F100").Find(what:=WHAT_TO_FIND, lookat:=xlWhole)

If you don't know the fixed last row, you can use as follow.

Set FoundCell = ws.Range("F8:F" & ws.Range("F8").SpecialCells(xlCellTypeLastCell).Row).Find(what:=WHAT_TO_FIND, lookat:=xlWhole)

where, ws.Range("F8").SpecialCells(xlCellTypeLastCell).Row get the last used cell.