jholokai02 0 Newbie Poster

Below I have listed my code. I am having a problem searching through multiple sheets in an excel workbook. As of now, it will load cboPcMark with only the first page of search results. It will also duplicate a couple of them. Can someone please take a look. Thanks in advance.:eek:

cboPcMark.Clear

Dim objExcel As Object     ' Excel application
Dim objBook As Object      ' Excel workbook
Dim objSheet As Object     ' Excel Worksheet

Dim ExcelCutlist As String
Dim rng As Range
Dim firstAddress As String
Dim PcMark As String
'Dim objSheet As Excel.Sheets


ExcelCutlist = App.Path & "\" & "TANA EC 271 I.xls"

Set objExcel = CreateObject("excel.application")
Set objBook = objExcel.Workbooks.Open(ExcelCutlist)
'Set objSheet = objBook.Worksheets.Item(1)

objExcel.Application.Visible = True

For Each objSheet In objBook.Worksheets
        
        Set rng = Nothing
        With objSheet.Cells
        Set rng = objSheet.Range("D:E").Find(What:=txtHeat.Text, lookin:=xlValues, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False)

                If Not rng Is Nothing Then
                    firstAddress = rng.Address
                        Do
                            cboPcMark.AddItem Range(rng.Address).Offset(0, -4)
                            Set rng = objSheet.FindNext(rng)
                        Loop While Not rng Is Nothing And rng.Address <> firstAddress
                End If
        End With

Next objSheet

Set objSheet = Nothing
If Not objBook Is Nothing Then objBook.Close
Set objBook = Nothing
objExcel.Quit
Set objExcel = Nothing