Sort four integers

[Dmitry_V]

Hi,

I have four integers stored in four variables. I need to sort these, and in a descending order put into appropriate cells (A1, B1, C1, D1). However, there also can be a string instead of one integer

I managed to find the integers, and store them into variables. I also managed to identify if string is selected and store it into appropriate cell (must be A1).

But how do I fianalize macro with sorting four / three integers and putting into appropriate cells? I mean the elegant way, not the ultra-super-If-Else-if type of code, but using probably the BubbleSort?

Here's the begging of the code:

Dim wb As Workbook, ws_wk_dlt As Worksheet, ws_dash As Worksheet, _
frcst_act As Variant, SOP_numb As Integer, SOP_year As Integer, cell_B6 As Variant, _
cell_C6 As Variant, cell_D6  As Variant, cell_E6 As Variant, SOP_key_B6 As Integer, SOP_key_C6 As Integer, SOP_key_D6 As Integer, SOP_key_E6 As Integer


'Referencing
Set wb = ThisWorkbook
Set ws_wk_dlt = wb.Worksheets("UMBCCA PivotDelta (wk)")
Set ws_dash = wb.Worksheets("UMBCCA Dashboard")


'Values from pivot stored
cell_B6 = ws_wk_dlt.Range("B6").Value
cell_C6 = ws_wk_dlt.Range("C6").Value
cell_D6 = ws_wk_dlt.Range("D6").Value
cell_E6 = ws_wk_dlt.Range("E6").Value


    'If len certain amount of chartacters then do option 1, or option 2
    If cell_B6 <> "" Then
        If Len(cell_B6) = 12 And cell_B6 <> "Actual Sales" Then
                SOP_key_B6 = CInt(Mid(cell_B6, 4, 2)) + CInt(Mid(cell_B6, 8, 4))
        ElseIf Len(cell_B6) = 11 And cell_B6 <> "Actual Sales" Then
            SOP_key_B6 = CInt(Mid(cell_B6, 4, 2)) + CInt(Mid(cell_B6, 7, 4))
        End If
    End If
        
    If cell_C6 <> "" Then
        If Len(cell_C6) = 12 And cell_C6 <> "Actual Sales" Then
                SOP_key_C6 = CInt(Mid(cell_C6, 4, 2)) + CInt(Mid(cell_C6, 8, 4))
        ElseIf Len(cell_C6) = 11 And cell_C6 <> "Actual Sales" Then
            SOP_key_C6 = CInt(Mid(cell_C6, 4, 2)) + CInt(Mid(cell_C6, 7, 4))
        End If
    End If
    
    If cell_D6 <> "" Then
        If Len(cell_D6) = 12 And cell_D6 <> "Actual Sales" Then
                SOP_key_D6 = CInt(Mid(cell_D6, 4, 2)) + CInt(Mid(cell_D6, 8, 4))
        ElseIf Len(cell_D6) = 11 And cell_D6 <> "Actual Sales" Then
            SOP_key_D6 = CInt(Mid(cell_D6, 4, 2)) + CInt(Mid(cell_D6, 7, 4))
        End If
    End If
    
    If cell_E6 <> "" Then
        If Len(cell_E6) = 12 And cell_E6 <> "Actual Sales" Then
                SOP_key_E6 = CInt(Mid(cell_B6, 4, 2)) + CInt(Mid(cell_E6, 8, 4))
        ElseIf Len(cell_E6) = 11 And cell_E6 <> "Actual Sales" Then
            SOP_key_E6 = CInt(Mid(cell_E6, 4, 2)) + CInt(Mid(cell_E6, 7, 4))
        End If
    End If


    'Finding the Actual Sales and putting into L30
    If cell_B6 = "Actual Sales" Then
        ws_dash.Range("A1").Value = cell_B6
    ElseIf cell_C6 = "Actual Sales" Then
        ws_dash.Range("A1").Value = cell_C6
    ElseIf cell_D6 = "Actual Sales" Then
        ws_dash.Range("A1").Value = cell_D6
    ElseIf cell_E6 = "Actual Sales" Then
        ws_dash.Range("A1").Value = cell_E6
    End If

 

[oldbrewer]

		let integers be 27, 49, 13, 99 n(1)=27 n(2)=49 n(3)=13 n(4)=99 let integers be 14, apple, 77,34 for k=1 to 4 for j=2 to 4 if(n(j) < n(j-1) then temp=n(j):n(j)=n(j-1):n(j-1)=temp next j next k in the second example you would only consider n(2) to n(4) for k=1 to 3 for j=3 to 4 etc <colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[shg]

Why not just put the values in the cells and then sort?

  Dim i1            As Integer
  Dim i2            As Integer
  Dim i3            As Integer
  Dim i4            As Integer

  i1 = 1000 * Rnd
  i2 = 1000 * Rnd
  i3 = 1000 * Rnd
  i4 = 1000 * Rnd

  With Range("A1:D1")
    .Value = Array(i1, i2, i3, i4)
    .Sort Key1:=.Cells, Order1:=xlDescending, Orientation:=xlSortRows
  End With

 

[Dmitry_V]

Hi oldbrewer,

Thanks. I see you only sort the integers, but how do you proceed further by putting into corresponding cells as in my example A1:D1? Another loop? I do understand it's a time consuming thing to do, but maybe you could be so kind and interpret your specific example with my code above?

 

[Dmitry_V]

Thanks shg,

I believe if there is a string value instead of one integer then the multiplication will fail and error will be generated? Thanks, though

 

[shg]

The multiplication was just to generate random numbers for demonstration.