Microsoft Excel - Find text in a row

Public Sub CopyData(ByVal rngHeader As Range, ByVal strMonth As String)

    Dim arrHeader       As Variant

    Dim intPos        As Integer

    arrHeader = rngHeader
    For intPos = LBound(arrHeader, 2) To UBound(arrHeader, 2)
      If arrHeader(1, intPos) = strMonth Then
        rngHeader.Cells(1, intPos).Offset(1, 0) = 1
        Exit For
      End If
    Next intPos

    If intPos > UBound(arrHeader, 2) Then
      MsgBox "Invalid Month input", vbOKOnly
    End If

End Sub

I didn't do the application.match because it would return an error if it wasn't found.  You would call it in VBA like:

call copydata(worksheets("Sheet1").Range("H10:S10"), "Jun")

If you had a range to paste into it, you could do something like this:

Public Sub CopyData(ByVal rngHeader As Range, ByVal strMonth As String, ByVal rngCopy As Range)

    Dim arrHeader       As Variant

    Dim intPos        As Integer

    arrHeader = rngHeader
    For intPos = LBound(arrHeader, 2) To UBound(arrHeader, 2)
      If arrHeader(1, intPos) = strMonth Then
        rngHeader.Cells(1, intPos).Offset(1, 0).Resize(rngCopy.Rows.Count, 1) = rngCopy.Value
        Exit For
      End If
    Next intPos

    If intPos > UBound(arrHeader, 2) Then
      MsgBox "Invalid Month input", vbOKOnly
    End If

End Sub

Try this Macro-
 


Public Function FindRowPos(sText As Variant, _
  Optional SearchDirection As XlSearchDirection = xlNext, _
  Optional SearchOrder As XlSearchOrder = xlByRows) As Long
 
    Dim lResult As Long, oRg As Range
 
    Set oRg = Cells.Find(What:=sText, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=SearchOrder, _
                 SearchDirection:=SearchDirection, _
    MatchCase:=False, SearchFormat:=False)
 
    If Not oRg Is Nothing Then lResult = oRg.Row
 
    FindRowPos = lResult
 
    Set oRg = Nothing
End Function

 
Try this and let me know.