Option Explicit

Sub egorr()
    Dim rng As Range, cell As Range, ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ws
        Set cell = .Range("A2")

        For Each rng In .Range("B2:F" & .Cells(.Rows.Count, 6).End(xlUp).Row)

            If rng.Value = 10 Then
                cell.Value = 1
            ElseIf rng.Value = 11 Then
                cell.Offset(1, 0).Value = 2
            ElseIf rng.Value = 12 Then
                cell.Offset(2, 0).Value = 3
            ElseIf rng.Value = 13 Then
                cell.Offset(3, 0).Value = 4
            ElseIf rng.Value = 14 Then
                cell.Offset(4, 0).Value = 5
                ' и т.д.
            End If

        Next

    End With

End Sub

            , ...................................
            If rng.Value = 28 Then
            ThisWorkbook.Worksheets("Лист2").Cells(rng+2,"BL").Value = 1

Option Explicit

Sub egorr_2()
    Dim rng         As Range
    Dim ws          As Worksheet: Set ws = ThisWorkbook.Worksheets("Лист1")
    Dim sh          As Worksheet: Set sh = ThisWorkbook.Worksheets("Лист2")
    Dim i As Long, j As Long

    With ws

        For i = 4 To .Cells(.Rows.Count, 7).End(xlUp).Row

            For j = 7 To 16

                If .Cells(i, j).Value = 28 Then
                    sh.Cells(i - 2, 64).Value = 1
                ElseIf .Cells(i, j).Value = 51 Then
                    sh.Cells(i - 2, 10).Value = 2
                    ' и т.д.продолжаете с ElseIf
                End If

            Next

        Next

        If .Range("G4:P" & .Cells(.Rows.Count, 1).End(xlUp).Row).Find("*") Is Nothing Then
            sh.Range("H2").Value = 1
            sh.Range("H2").AutoFill Destination:=sh.Range("H2:H" & .Cells(.Rows.Count, 1).End(xlUp).Row), Type:=xlFillDefault
        End If

    End With

End Sub