Arrange numbers in the row according to cells with value only

Hi, there!!
I have the macro which takes the values from the table and write them into given cells in the row:

Sub SortingGupta()
'
' SortingGupta Macro
' Sorting jobs according to given K

Dim i As Integer, k As Integer

 i = 0
 For k = 0 To 29
 Worksheets("Gupta").Cells(45, Chr(66 + i)) = Worksheets("Gupta").Cells(15 + k, "C")
 i = i + 2
 k = k + 2
 Next k

End Sub

Row looks like this:

1.JPG

My goal now is rearrange the numbers in the same row from min to max. I've tried to use excel build in "Sort" function but it's destroy the row after values sorting:

2.JPG

I need to keep the order (value-arrow, value-arrow...)
it is possible to make it work somehow using vba (add the code into existing macro)?
Thank you in advance,
Igor

You should describe the actual setup of your sheet, or post a workbook, rather than a trimmed picture that shows nothing useful. Anyway, the better way is to not sort, but assign the values to the cells, like so, which will arrange the values from lowest to highest, in columns B, D, F, .... Z of row 45.

Sub SortingEveryOtherColumnInARow()
Dim v As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
With Worksheets("Gupta")
For i = 45 To 45 'change if you need different or more rows
v = Intersect(.Range("B:Z"), .Rows(i)).Value 'get values from columns B to Z
k = 1
For j = 2 To 26 Step 2
.Cells(i, j).Value = Application.WorksheetFunction.Small(v, k)
k = k + 1
Next j
Next i
End With
End Sub

You need to run your macro first, to put values in, and change the range from B:Z to B:T, and the upper on the loop. The macros can be combined like so - Note that the Chr(...) to return the column letter breaks down after Z, so better to use a number, and it is always a bad idea to increment your loop control variable within the loop.

Sub SortingGuptaV2()
    Dim v As Variant
    Dim lngRow As Long
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    With Worksheets("Gupta")
        i = 2
        For j = 1 To 10
            .Cells(45, i).Value = .Cells(15 + k, "C").Value
            i = i + 2
            k = k + 3
        Next j
        
        lngRow = 45 'sort row 45
        
        v = Intersect(.Range("B:T"), .Rows(lngRow)).Value 'get values from columns B to Z
    
        For j = 2 To 20 Step 2
            .Cells(lngRow, j).Value = Application.WorksheetFunction.Small(v, j / 2)
        Next j

    End With
End Sub

WOW!!
Thanks for quick replay!! Now it's working!!
I'm just learning to use vba so you help is much appreciated!
May be you can help me with additional question...
Within the same file under A45 (k = 1) row I have "Job" row.
So in this row I should arrange job numbers (1,2,3,4,5,6,7,8,9,10) according to the colored values it the table.
For example if value "-0.17" belong to job "1" in the "Job" row I should write "1" under "-0.17" value.
In other words after values are arranged from min to max in the row "job" should be job numbers arranged accordingly.
Than you in advance,
igor

When you need to sort values keeping things together, it is better to create a range to sort elsewhere. Try it this way, which assumes that rows 145 and 146 are blank.

Sub SortingGuptaV3()
    Dim v As Variant
    Dim lngRow As Long
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    With Worksheets("Gupta")
        
        For j = 1 To 10
            .Cells(145, j).Value = .Cells(15 + k, "C").Value
            .Cells(146, j).Value = .Cells(14 + k, "A").Value
            k = k + 3
         Next j
           
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Cells(145, 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SetRange .Cells(145, 1).Resize(2, 10)
        .Sort.Header = xlNo
        .Sort.Orientation = xlLeftToRight
        .Sort.Apply
    
        For j = 1 To 10
            .Cells(45, j * 2).Value = .Cells(145, j).Value
            .Cells(46, j * 2).Value = .Cells(146, j).Value
        Next j
        
        .Cells(145, 1).Resize(2, 10).Clear
    End With
End Sub