How to find exact cell value in column?-CodePudding

I have a problem with finding my cell value in whole workbook. My code so far :

Workbooks("someworkbook").Activate
With ThisWorkbook
    Dim k, cn As Integer
    Dim wsn As Integer
    dim komabs as string
    komabs="somestring"
    wsn = Workbooks("someworkbook").Sheets.Count

    For cn = 1 To wsn
        Worksheets(cn).Activate
        n = Worksheets(cn).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
        For k = 2 To n
            If Cells(k, 1).Value = komabs Then
                MsgBox "sth is here "
                Cells(k, 1).Select
            Else 'If Not Cells(n, 1).Value = komabs Then
                Exit For 
            End If
        Next k
    Next cn
End With

The problem with this code is that when it doesn't find value in first cell, he goes to next worksheet which would be just fine if he cycled through all the cells in first worksheet and only then would go to the next one.

CodePudding user response：

Find First Cell Containing String By Searching in the Same Column of All Worksheets

Option Explicit

Sub FindStringInColumn()
    
    Const SearchString As String = "somestring"
    
    Dim swb As Workbook: Set swb = Workbooks("Test.xlsx") ' ThisWorkbook
    
    Dim sws As Worksheet
    Dim sCell As Range
    Dim sIndex As Variant
    
    For Each sws In swb.Worksheets
        Dim srg As Range: Set srg = sws.UsedRange.Columns("A")
        sIndex = Application.Match(SearchString, srg, 0)
        If IsNumeric(sIndex) Then
            Set sCell = srg.Cells(sIndex)
            Exit For
        End If
    Next sws
        
    ' Continue using the worksheet and its cell:
    
    swb.Activate ' make sure the correct workbook is active...
    sws.Select ' ... and make sure the correct worksheet is active...
    sCell.Select ' ... if you really need to select the cell
    
    MsgBox "The string '" & SearchString & "' was found in cell '" _
        & sCell.Address(0, 0) & "' of worksheet '" & sws.Name & "'.", _
        vbInformation

End Sub

CodePudding user response：

i think it is because of exit for, try this:

Workbooks("someworkbook").Activate
With ThisWorkbook
    Dim k, cn As Integer
    Dim wsn As Integer
    dim komabs as string
    komabs="somestring"
    wsn = Workbooks("someworkbook").Sheets.Count

    For cn = 1 To wsn
        Worksheets(cn).Activate
        n = Worksheets(cn).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
'---> maybe u can use end(xlup)
        For k = 2 To n
            If Cells(k, 1).Value = komabs Then
                'MsgBox "sth is here "
                Cells(k, 1).Select
                MsgBox "sth is here "
            'Else 'If Not Cells(n, 1).Value = komabs Then
            ' dont exit for -->  Exit For 
            End If
        Next k
    Next cn
End With