I want to find a value only first row of my excel file. but my code, find all my sheet.

column = oBook.Sheets("Sheet1").Cells().Find(What:=CStr(size)).column

CodePudding user response：

Please, try the next way:

 Dim rngSize As Range, column As Long
 Set rngSize = oBook.Sheets("Sheet1").Rows(1).Find(What:=CStr(size)) 
 If Not rngSize Is Nothing Then 
    column = rngSize.Column 
 Else 
    MsgBox size & " string could not be found in the first row..."
 End If

CodePudding user response：

Find In First Row (Application.Match)

• Using Application.Match is simpler and more efficient. You can do the same with the Find method but there are a few more arguments that you need to use.

Sub FindColumn()
    
    Const Size As Double = 3.14
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' adjust!
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    Dim rrg As Range: Set rrg = ws.Rows(1)
    
    Dim CritString As String
    CritString = "*" & CStr(Size) & "*" ' contains
    
'    ' or
'    CritString = CStr(Size) & "*" ' begins with
'    ' or
'    CritString = "*" & CStr(Size) ' ends with
'    ' or
'    CritString = CStr(Size) ' number in a cell previously formatted as text
'    ' or
'    ' Use 'Size' if it's a number
        
    Dim SizeColumn As Variant ' error if not found
    SizeColumn = Application.Match(CritString, rrg, 0)
    
    If IsError(SizeColumn) Then Exit Sub
    
    ' Continue if found e.g.:
    MsgBox "The column for size " & Size & " is " & SizeColumn & ".", _
        vbInformation
    
End Sub