=ПОИСКПОЗ(НАИБОЛЬШИЙ(B2:F9;3);B2:F9;0)

1. Application.Match
2. Application.WorksheetFunction.Match

    Set wb = Workbooks.Open(MyPath, 0, 1)
    If wb Is Nothing Then GoTo ErrorHandler
    For Each MySheet In wb.Worksheets 'Ошибка возникает на данной строке, книга открывается но цикл по листам не хочет работать
    'тут код
    Next

On Error GoTo 0

Sub test()

For i = 1 To 10

On Error GoTo ErrorHendler1

i = 2 / 0

On Error GoTo 0

afterBadComment:
'Тут код на который не должна распространяться обработка ошибок


Next i

ErrorHendler1:

'Тут обработчик ошибок
GoTo afterBadComment


End Sub

On Error GoTo 0

For Each NM In ThisWorkbook.Sheets(1).Names
     rangeAddress = range(NM).Address 'возвращает диапазон - "$A$10"
     nameAddress = range(NM).Name     'возвращает диапазон - "=Заявка!$A$10"
Next

Sub QuickSort(arr, Lo As Long, Hi As Long)
  Dim varPivot As Variant
  Dim varTmp As Variant
  Dim tmpLow As Long
  Dim tmpHi As Long
  tmpLow = Lo
  tmpHi = Hi
  varPivot = arr((Lo + Hi) \ 2)
  Do While tmpLow <= tmpHi
    Do While arr(tmpLow) < varPivot And tmpLow < Hi
      tmpLow = tmpLow + 1
    Loop
    Do While varPivot < arr(tmpHi) And tmpHi > Lo
      tmpHi = tmpHi - 1
    Loop
    If tmpLow <= tmpHi Then
      varTmp = arr(tmpLow)
      arr(tmpLow) = arr(tmpHi)
      arr(tmpHi) = varTmp
      tmpLow = tmpLow + 1
      tmpHi = tmpHi - 1
    End If
  Loop
  If Lo < tmpHi Then QuickSort arr, Lo, tmpHi
  If tmpLow < Hi Then QuickSort arr, tmpLow, Hi
End Sub

Sub SaveSheet()
Application.ScreenUpdating = False
Dim FileN$
FileN = ThisWorkbook.Path & "\" & "Порученние" & Date & ".xlsx"
ThisWorkbook.Sheets(1).Copy
ActiveWorkbook.SaveCopyAs FileN
ActiveWorkbook.Close SaveChanges:=False
MsgBox "Поручение сохранено" & Chr(13) & FileN
ThisWorkbook.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

Public Function TP(TypeP As Variant, Town As Variant)
Application.Volatile (True)
TP = Application.WorksheetFunction.CountIfs(ThisWorkbook.ActiveSheet.Range(Cells(2, Town.Column), Cells(Town.Row, Town.Column)), Town, _
ThisWorkbook.ActiveSheet.Range(Cells(2, TypeP.Column), Cells(TypeP.Row, TypeP.Column)), TypeP)
End Function

Totality = Application.CountIf(Range(Cells(1, 1), Cells(StrokaFin, 1)), ??? )

Function Send(Poluchatel As Variant, Tema As String, Telo As Variant, PoluchatelCopy As Variant, strAttachment1 As String)

'Отправка результата письмом через LotusNotes
Dim Maildb As Object
Dim MailDoc As Object
Dim Body As Object
Dim Session As Object


Set Session = CreateObject("Notes.NotesSession")
Set Maildb = Session.GetDatabase("LN1_Ulii/Ulii", Otpr(VBA.Environ("UserName"), 3))
Set MailDoc = Maildb.CreateDocument
    MailDoc.Body = Telo
    MailDoc.SendTo = Poluchatel
    MailDoc.Form = "Memo"
    MailDoc.CopyTo = ""
    MailDoc.BlindCopyTo = PoluchatelCopy
    MailDoc.Subject = Tema

    Set objAttachMe = MailDoc.CreateRichTextItem("Attachment1")
    Set objEmbedObj = objAttachMe.EmbedObject(1454, "", strAttachment1, "Attachment")
    Set objAttachMe = MailDoc.CreateRichTextItem("Attachment1")

    Call MailDoc.REPLACEITEMVALUE("PostedDate", Now())
    Call MailDoc.Send(False)
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set Body = Nothing
    Set Session = Nothing
End Function

Workbooks.Open Filename:="C:\Users\Desktop\источник.xlsx", ReadOnly:=True

Thisworkbook.sheets(1).cells(i, 9) = Application.WorksheetFunction.Average(a, b, c)

idproc = Shell("C:\Program Files\Microsoft Office\Office14\EXCEL.EXE", 3)

Dim app As New Excel.Application
Set WbRab = app.Workbooks.Open("C:\Users\U12345\Desktop\t.xlsm", 0, 1)