Sorry - I'm a bonehead. I though you had the numbers 1 to whatever instead of random numbers.

Sub TestMacro5numbersV2()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim c As Integer
Dim iTar As Integer

iTar = 40 'Value you need to sum to....

c = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To c - 3
For j = i + 1 To c - 2
For k = j + 1 To c - 1
For l = k + 1 To c
If 2 * Cells(i, 1) + Cells(j, 1) + Cells(k, 1) + Cells(l, 1) = iTar Then
MsgBox Cells(i, 1) & " + " & Cells(i, 1) & " + " & Cells(j, 1) & " + " & Cells(k, 1) & " + " & Cells(l, 1)
End If
If Cells(i, 1) + 2 * Cells(j, 1) + Cells(k, 1) + Cells(l, 1) = iTar Then
MsgBox Cells(i, 1) & " + " & Cells(j, 1) & " + " & Cells(j, 1) & " + " & Cells(k, 1) & " + " & Cells(l, 1)
End If
If Cells(i, 1) + Cells(j, 1) + 2 * Cells(k, 1) + Cells(l, 1) = iTar Then
MsgBox Cells(i, 1) & " + " & Cells(j, 1) & " + " & Cells(k, 1) & " + " & Cells(k, 1) & " + " & Cells(l, 1)
End If
If Cells(i, 1) + Cells(j, 1) + Cells(k, 1) + 2 * Cells(l, 1) = iTar Then
MsgBox Cells(i, 1) & " + " & Cells(j, 1) & " + " & Cells(k, 1) & " + " & Cells(l, 1) & " + " & Cells(l, 1)
End If
Next l
Next k
Next j
Next i

End Sub