Hi All,
I am trying to find a particular value in the list with SUM function.
Example: 100, 120, 150, 180, 120, 100, 140
And the result I need should be 450, whereas if you add the 150, 180 & 120 from the list, we will get that particular value (450).
I looked on internet and I found the below code to do that.
Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
Dim xStr As String
Dim xSum As Double
Dim xCell As Range
xSum = SumCellId
For Each xCell In CoinsRange
If Not (xSum / xCell < 1) Then
xStr = xStr & Int(xSum / xCell) & " of " & xCell & " "
xSum = xSum - (Int(xSum / xCell)) * xCell
End If
Next
GetCombination = xStr
End Function
Like i will be getting it as 1 of 150, 1 of 180, 1 of 120, but the problem is if the list has 150, 150, 150, 150, 100, 100, 120, I am getting the result as "3 of 150".
What I need is let the list in Range ("A") column, and the result I am waiting for will be in Range("B") column, All I want is, I need only the numbers which are the exact results should be listed in Range("C") column without this "1 of" or "2 of".
Can anyone give it a try.
Thanks in advance
I am trying to find a particular value in the list with SUM function.
Example: 100, 120, 150, 180, 120, 100, 140
And the result I need should be 450, whereas if you add the 150, 180 & 120 from the list, we will get that particular value (450).
I looked on internet and I found the below code to do that.
Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
Dim xStr As String
Dim xSum As Double
Dim xCell As Range
xSum = SumCellId
For Each xCell In CoinsRange
If Not (xSum / xCell < 1) Then
xStr = xStr & Int(xSum / xCell) & " of " & xCell & " "
xSum = xSum - (Int(xSum / xCell)) * xCell
End If
Next
GetCombination = xStr
End Function
Like i will be getting it as 1 of 150, 1 of 180, 1 of 120, but the problem is if the list has 150, 150, 150, 150, 100, 100, 120, I am getting the result as "3 of 150".
What I need is let the list in Range ("A") column, and the result I am waiting for will be in Range("B") column, All I want is, I need only the numbers which are the exact results should be listed in Range("C") column without this "1 of" or "2 of".
Can anyone give it a try.
Thanks in advance