find the best match with sizing based on a certain sum VBA

S

Sotos13

New Member
Joined
Mar 8, 2019
Messages
42
Hello everyone,

i have this problem i have to solve. It's not that easy but here it is .... In column A i have a list of sizes maybe Hundreds Thousands.. and they might be from 10 to 100 in column B. i also have a certain target range for example 95 to 100.
i want to add all numbers in column B in order to result from 90 to 100(my target range) for example A1 with A10 = 100, A2+A3+ A11 =90, A4+A6 =90, then A5+A12 = 95 and so on.. The numbers which are used cannot be used again. The result will be a table with this info as the table below SIZE 1 and SIZE 10 100 etc or A1 and A10 something like that. I know it's hard but if anyone has an idea please be free to share

SIZE 1 90 A1+A10 100 Range 90- 100RESULTS
SIZE 2 50 A2+A3+A1190SIZE 1 and SIZE 10100
SIZE 330SIZE 2 and SIZE 3 and SIZE 1190
SIZE 460 A4+A690SIZE 4 and SIZE 690
SIZE 570 A5+A1295SIZE 5 and SIZE 1295
SIZE 630SIZE 7 and SIZE 16100
SIZE 780 A7+A16100
SIZE 840
SIZE 980
SIZE 1010
SIZE 1110
SIZE 1225
SIZE 1335
SIZE 1475
SIZE 1595
SIZE 1620

<tbody>
</tbody>
Thanks

<tbody>
</tbody>
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
I give you an approach only with the sum of 2 numbers.
Put your data as shown in the following figure.
In A your size
In B your numbers
In D2 from
In E2 To


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:8pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:46.57px;" /><col style="width:64.63px;" /><col style="width:23.76px;" /><col style="width:27.56px;" /><col style="width:32.32px;" /><col style="width:25.66px;" /><col style="width:115.01px;" /><col style="width:26.61px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">SIZE</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">NUMBERS</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; "> </td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">RANGE INI</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">RANGE END</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; "> </td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">RESULTS</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:11pt; ">SIZE 1</td><td style="font-size:11pt; text-align:right; ">90</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; text-align:right; ">90</td><td style="font-size:11pt; text-align:right; ">100</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 1 and SIZE 10</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:11pt; ">SIZE 2</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 2 and SIZE 8</td><td style="font-size:11pt; text-align:right; ">90</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:11pt; ">SIZE 3</td><td style="font-size:11pt; text-align:right; ">30</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 3 and SIZE 5</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:11pt; ">SIZE 4</td><td style="font-size:11pt; text-align:right; ">60</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 4 and SIZE 6</td><td style="font-size:11pt; text-align:right; ">90</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:11pt; ">SIZE 5</td><td style="font-size:11pt; text-align:right; ">70</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 7 and SIZE 16</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:11pt; ">SIZE 6</td><td style="font-size:11pt; text-align:right; ">30</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 9 and SIZE 11</td><td style="font-size:11pt; text-align:right; ">90</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:11pt; ">SIZE 7</td><td style="font-size:11pt; text-align:right; ">80</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 12 and SIZE 14</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:11pt; ">SIZE 8</td><td style="font-size:11pt; text-align:right; ">40</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 15</td><td style="font-size:11pt; text-align:right; ">95</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:11pt; ">SIZE 9</td><td style="font-size:11pt; text-align:right; ">80</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:11pt; ">SIZE 10</td><td style="font-size:11pt; text-align:right; ">10</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:11pt; ">SIZE 11</td><td style="font-size:11pt; text-align:right; ">10</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:11pt; ">SIZE 12</td><td style="font-size:11pt; text-align:right; ">25</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:11pt; ">SIZE 13</td><td style="font-size:11pt; text-align:right; ">35</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:11pt; ">SIZE 14</td><td style="font-size:11pt; text-align:right; ">75</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:11pt; ">SIZE 15</td><td style="font-size:11pt; text-align:right; ">95</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:11pt; ">SIZE 16</td><td style="font-size:11pt; text-align:right; ">20</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr></table>
 
Upvote 0
Run this code:

Code: 
Sub find_best_match()
    Dim lr As Long, t_ini As Long, t_end As Long, goal As Long, cad As String
    Dim i As Long, j As Long, k As Long, r As Range, b As Range, cell As String
    
    Application.ScreenUpdating = False
    
    Range("C:C").ClearContents
    Range("G2:H" & Rows.Count).ClearContents
    
    lr = Range("A" & Rows.Count).End(xlUp).Row
    t_ini = Range("D2")
    t_end = Range("E2")
    k = 2
    
    For i = 2 To lr
        cad = Cells(i, "A")
        goal = t_end - Cells(i, "B")
        If Cells(i, "C").Value = "" Then
            If goal > 0 Then
                For j = 1 To 3
                    Set r = Range(Cells(i + 1, "B"), Cells(lr, "B"))
                    Set b = r.Find(goal, LookAt:=xlWhole, LookIn:=xlValues)
                    If Not b Is Nothing Then
                        cell = b.Address
                        Do
                            If b.Offset(0, 1).Value = "" Then
                                cad = cad & " and " & b.Offset(0, -1)
                                Cells(k, "G").Value = cad
                                Cells(k, "H").Value = Cells(i, "B").Value + Cells(b.Row, "B").Value
                                Cells(i, "C").Value = "x"
                                Cells(b.Row, "C").Value = "x"
                                k = k + 1
                                Exit For
                            End If
                            Set b = r.FindNext(b)
                        Loop While Not b Is Nothing And b.Address <> cell
                        
                    End If
                    If j = 1 Then
                        goal = t_ini - Cells(i, "B")
                    Else
                        goal = ((t_ini + t_end) / 2) - Cells(i, "B")
                    End If
                Next
            Else
                Cells(k, "G").Value = cad
                Cells(k, "H").Value = Cells(i, "B").Value
                Cells(i, "C").Value = "x"
            End If
            If Cells(i, "B").Value >= t_ini And Cells(i, "B").Value <= t_end Then
                Cells(k, "G").Value = cad
                Cells(k, "H").Value = Cells(i, "B").Value
                Cells(i, "C").Value = "x"
            End If
        End If
    Next
    MsgBox "Done"
End Sub
 
Upvote 0
Hi Dante

Really impressive and it works , but as you said only for two numbers. For example i changed the numbers and it leaves alot of them outide the calculation.See table. What should we change in order to add more numbers?

sizenumbersRange inRange to
SIZE 11090100SIZE 3 and SIZE 890
SIZE 220SIZE 4 and SIZE 7100
SIZE 330xSIZE 9 and SIZE 20100
SIZE 450xSIZE 10 and SIZE 16100
SIZE 520SIZE 19 and SIZE 22100
SIZE 610
SIZE 750x
SIZE 860x
SIZE 940x
SIZE 1050x
SIZE 1120
SIZE 1225
SIZE 1310
SIZE 1420
SIZE 1530
SIZE 1650x
SIZE 1720
SIZE 1810
SIZE 1950x
SIZE 2060x
SIZE 2140
SIZE 2250x

Thanks again

<colgroup><col span="3"><col><col span="2"><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
also Dante, the numbers that are over a limit are nit presented in the results ..see the table for size 6 120


sizenumbersRange inRange to
180x1151251 and 21125
2110x2 and 29125
370x3 and 9125
4115x4 and 75125
560x5 and 25125
6120x7 and 83125
7105x8 and 26125
880x11 and 42125
955x12 and 19125
10120x13 and 67125
1170x14 and 78125
1250x15 and 34125
1360x16 and 45125
1460x17 and 44125
1540x18 and 30125



Regards and thanks for all your help

<colgroup><col span="3"><col><col span="2"><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
I updated the macro

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:8pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:46.57px;" /><col style="width:66.53px;" /><col style="width:13.31px;" /><col style="width:23.76px;" /><col style="width:31.37px;" /><col style="width:25.66px;" /><col style="width:280.4px;" /><col style="width:35.17px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">SIZE</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">NUMBERS</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; "> </td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">INI</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">END</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; "> </td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">RESULTS</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">SUM</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:11pt; ">SIZE 1</td><td style="font-size:11pt; text-align:right; ">10</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; text-align:right; ">90</td><td style="font-size:11pt; text-align:right; ">100</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 1 and SIZE 2 and SIZE 3 and SIZE 9</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:11pt; ">SIZE 2</td><td style="font-size:11pt; text-align:right; ">20</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 4 and SIZE 7</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:11pt; ">SIZE 3</td><td style="font-size:11pt; text-align:right; ">30</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 5 and SIZE 6 and SIZE 8 and SIZE 13</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:11pt; ">SIZE 4</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 10 and SIZE 16</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:11pt; ">SIZE 5</td><td style="font-size:11pt; text-align:right; ">20</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 11 and SIZE 12 and SIZE 19</td><td style="font-size:11pt; text-align:right; ">95</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:11pt; ">SIZE 6</td><td style="font-size:11pt; text-align:right; ">10</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 14 and SIZE 15 and SIZE 21</td><td style="font-size:11pt; text-align:right; ">90</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:11pt; ">SIZE 7</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 17 and SIZE 18 and SIZE 20</td><td style="font-size:11pt; text-align:right; ">90</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:11pt; ">SIZE 8</td><td style="font-size:11pt; text-align:right; ">60</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:11pt; ">SIZE 9</td><td style="font-size:11pt; text-align:right; ">40</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:11pt; ">SIZE 10</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:11pt; ">SIZE 11</td><td style="font-size:11pt; text-align:right; ">20</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:11pt; ">SIZE 12</td><td style="font-size:11pt; text-align:right; ">25</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:11pt; ">SIZE 13</td><td style="font-size:11pt; text-align:right; ">10</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:11pt; ">SIZE 14</td><td style="font-size:11pt; text-align:right; ">20</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:11pt; ">SIZE 15</td><td style="font-size:11pt; text-align:right; ">30</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:11pt; ">SIZE 16</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:11pt; ">SIZE 17</td><td style="font-size:11pt; text-align:right; ">20</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:11pt; ">SIZE 18</td><td style="font-size:11pt; text-align:right; ">10</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:11pt; ">SIZE 19</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:11pt; ">SIZE 20</td><td style="font-size:11pt; text-align:right; ">60</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:11pt; ">SIZE 21</td><td style="font-size:11pt; text-align:right; ">40</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:11pt; ">SIZE 22</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr></table>

Try:

Code: 
Option Explicit
Sub find_best_match()
    Dim lr As Long, t_ini As Long, t_end As Long, contador As Long, n As Long
    Dim i As Long, k As Long, m As Long, fut As Long, res As Long, f As Long
    Dim continue As Boolean, cad As String, yaesta As Boolean
    Dim filas As New Collection
    
    Application.ScreenUpdating = False
    
    Range("C:C").ClearContents
    Range("G2:H" & Rows.Count).ClearContents
    
    lr = Range("A" & Rows.Count).End(xlUp).Row
    t_ini = Range("E2")
    t_end = Range("E2")
    k = 2
    
    For i = 2 To lr
        cad = ""
        If Cells(i, "C").Value = "" Then
            filas.Add i
            res = Cells(i, "B").Value
            continue = True
            contador = i
            n = 1
            Do While contador < lr
                For m = contador + 1 To lr
                    yaesta = False
                    For f = 1 To filas.Count
                        If m = filas(f) Then
                            yaesta = True
                            Exit For
                        End If
                    Next
                    
                    If yaesta = False Then
                        If Cells(m, "C").Value = "" Then
                            fut = res + Cells(m, "B").Value
                            If fut >= t_ini And fut <= t_end Then
                                filas.Add m
                                For f = 1 To filas.Count
                                    cad = cad & " and " & Cells(filas(f), "A").Value
                                    Cells(filas(f), "C").Value = "x"
                                Next
                                Cells(k, "G").Value = Mid(cad, 6)
                                Cells(k, "H").Value = fut
                                k = k + 1
                                continue = False
                                Set filas = Nothing
                                Exit For
                            End If
                        End If
                    End If
                Next m
                If continue = False Then Exit Do
                contador = contador + 1
                If Cells(contador, "C").Value = "" Then
                    res = res + Cells(contador, "B")
                    filas.Add contador
                End If
                n = n + 1
                If n > 4 Then
                    t_ini = Range("D2")
                    Set filas = Nothing
                    i = i - 1
                    Exit Do
                End If
            Loop
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub
 
Upvote 0
Hi Dante
For some reason it not responding.
I thought it might had to do with the t_ini = Range("E2") t_end = Range("E2") which is the same so i change it to G2 but again nothing changed.
it'like it doesnt stop looping i think.

Thanks
Sotos
 
Upvote 0
Sotos13 said:
Hi Dante
For some reason it not responding.
I thought it might had to do with the t_ini = Range("E2") t_end = Range("E2") which is the same so i change it to G2 but again nothing changed.
it'like it doesnt stop looping i think.

Thanks
Sotos
Click to expand...


Perform a test with the data that is in the example.
From 90 To 100
This is correct:
Code: 
    t_ini = Range ("E2")
    t_end = Range ("E2")

It is to find the best. Later, if it does not find, the code changes to:


Code: 
                   t_ini = Range ("D2")

-----------------
Or write the data you are using to review them.
 
Upvote 0
Hi Dante

i tried with your data and it worked fine. then i added about 30 more lines.
Again it started not responding. I closed the window to restart excel and the result appeared. But it left the last numbers....Is there a chance that it cant find more than 4 sums?


SIZENUMBERS INIEND RESULTSSUM
SIZE 110x90100 SIZE 1 and SIZE 2 and SIZE 3 and SIZE 9100
SIZE 220x SIZE 4 and SIZE 7100
SIZE 330x SIZE 5 and SIZE 6 and SIZE 8 and SIZE 13100
SIZE 450x SIZE 10 and SIZE 16100
SIZE 520x SIZE 11 and SIZE 12 and SIZE 1995
SIZE 610x SIZE 14 and SIZE 15 and SIZE 2190
SIZE 750x SIZE 17 and SIZE 18 and SIZE 2090
SIZE 860x SIZE 22 and SIZE 26100
SIZE 940x SIZE 23 and SIZE 24 and SIZE 25 and SIZE 29100
SIZE 1050x SIZE 27 and SIZE 28 and SIZE 30 and SIZE 3190
SIZE 1120x SIZE 32 and SIZE 33 and SIZE 34100
SIZE 1225x
SIZE 1310x
SIZE 1420x
SIZE 1530x
SIZE 1650x
SIZE 1720x
SIZE 1810x
SIZE 1950x
SIZE 2060x
SIZE 2140x
SIZE 2250x
SIZE 2310x
SIZE 2420x
SIZE 2530x
SIZE 2650x
SIZE 2720x
SIZE 2810x
SIZE 2940x
SIZE 3050x
SIZE 3110x
SIZE 3220x
SIZE 3330x
SIZE 3450x
SIZE 3520
SIZE 3610
SIZE 3710
SIZE 3810
SIZE 3910
SIZE 4010
SIZE 4110
SIZE 4210
SIZE 4310
SIZE 4410
SIZE 4510
SIZE 4610
SIZE 4720
SIZE 4810
SIZE 4910

<colgroup><col span="6"><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Done, I already solved it

Code: 
Option Explicit
Sub find_best_match()
    Dim lr As Long, t_ini As Long, t_end As Long, contador As Long, n As Long
    Dim i As Long, k As Long, m As Long, fut As Long, res As Long, f As Long
    Dim continue As Boolean, cad As String, yaesta As Boolean
    Dim filas As New Collection
    
    Application.ScreenUpdating = False
    
    Range("C:C").ClearContents
    Range("G2:H" & Rows.Count).ClearContents
    
    lr = Range("A" & Rows.Count).End(xlUp).Row
    t_ini = Range("D2")
    t_end = Range("E2")
    k = 2
    
    For i = 2 To lr
        cad = ""
        If Cells(i, "C").Value = "" Then
            filas.Add i
            res = Cells(i, "B").Value
            continue = True
            contador = i
            n = 1
            Do While contador < lr
                For m = contador + 1 To lr
                    yaesta = False
                    For f = 1 To filas.Count
                        If m = filas(f) Then
                            yaesta = True
                            Exit For
                        End If
                    Next
                    
                    If yaesta = False Then
                        If Cells(m, "C").Value = "" Then
                            fut = res + Cells(m, "B").Value
                            If fut >= t_ini And fut <= t_end Then
                                filas.Add m
                                For f = 1 To filas.Count
                                    cad = cad & " and " & Cells(filas(f), "A").Value
                                    Cells(filas(f), "C").Value = "x"
                                Next
                                Cells(k, "G").Value = Mid(cad, 6)
                                Cells(k, "H").Value = fut
                                k = k + 1
                                continue = False
                                Set filas = Nothing
                                Exit For
                            End If
                        End If
                    End If
                Next m
                If continue = False Then Exit Do
                contador = contador + 1
                If Cells(contador, "C").Value = "" Then
                    fut = res + Cells(contador, "B").Value
                    If fut < t_end Then
                        res = res + Cells(contador, "B")
                        filas.Add contador
                    End If
                End If
                'n = n + 1
                'If n > 10 Then
                '    a = a
                '    t_ini = Range("D2")
                '    Set filas = Nothing
                '    i = i - 1
                '    Exit Do
                'End If
            Loop
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
IF and AND formulas for multiple criteria
Replies
6
Views
153
Peter_SSs
Peter_SSs
gtd526
  • Question
Concatenate cells Before a certain value
2
Replies
10
Views
246
etaf
etaf
B
  • Question
Conditional Formatting for multiple cells based on other cell value
Replies
4
Views
230
Benong
B
V
  • Solved
If function now working
Replies
4
Views
273
vmjan02
V
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
373
Legacy 352679
L

Forum statistics

Threads
1,214,515
Messages
6,119,973
Members
448,933
Latest member
Bluedbw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top