It seems best to work backwards and start by finding all the dyads in
column 4 that sum to 70 then test the column 1 and then the a's & b's
etc. That would be a pretty simple for/next loop. Of course, you better
have a fast processor if the number of tests is high.
Like below except I didn't pay attention to the A's and B's and its in
rudimentary basic not
vb.
If you knew something about the range of values in column 4 you could
probably speed things up a lot.
John
For n = 1 to length of column 4
For m = n+1 to length of column 4
IF Column4(n) + Coloumn4(m) 70 Then
IF Coloumn4(n) < Column1(n) or Column1(m) AND Coloumn4(m) <
Column1(n) or Column1(m) THEN
TestSum = Column3(n) + Column3(m)
IF TestSum Oldsum Then
Best_n = n : Best_m = M
Oldsum = TestSum
End if
End IF
End IF
Next
Print "The max sum is" Oldsum " of items" n","m
Mark wrote:
Dear EXPERT!
You be at home in VBA!
My simplification problem (warm-up)has resolved.
I have wanted to show selected elements and i have written
after line "max_sum" code:
If max_s < max_sum Then
Cells(2, 4) = cell1.Offset(0, -1)
Cells(3, 4) = cell2.Offset(0, -1)
Cells(2, 5) = cell1
Cells(3, 5) = cell2
max_s = max_sum
End If
My problem is complex. I'd like to show you a level highly:
column1 column2 column3 column4
1 A 200 10
1 A 30 50
1 B 40 40
2 A 50 40
2 B 10 20
2 A 50 50
....etc. thousand records
For each name (A, B, and someone else) in column2 i
looking for optimal sum_max of two elements in column3.
Nacessary conditions (important):
-sum of these two elements in column4 70.
-these two elements can't have the same number in column1
Result could be show in another sheet in shape:
Name in column2 "Sum_max is" .....
name of column1 name of column3 name of column4
element of col1 element of col3 element in col4
element of col1 element of col3 element in col4
Sum of two elements Sum of two elements
e.g.(hypothetical):
A Sum_max is 1200
column1 column3 column4
1 500 50
5 700 30
1200 80
below next names of column2
B ......
I would be very happy if you could help me in this.
Would you be possible use in this task table's variable?
Best wishes for Jarek
mark
-----Original Message-----
Is that what you are looking for?
Example for data in A2:B100
Sub test()
Dim cell1 As Range, cell2 As Range
For Each cell1 In Range("B2:B100")
For Each cell2 In Range("B2:B100")
If cell1 + cell2 70 And cell1.Address < cell2.Address
Then
max_sum = Application.Max(max_sum, cell1.Offset(0, -1) +
cell2.Offset(0, -1))
End If
Next cell2
Next cell1
MsgBox "Maximum of two elements in column A is " &
max_sum
End Sub
Jarek
--
Jarek
----------------------------------------------------------
--------------
Jarek's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=965
View this thread:
http://www.excelforum.com/showthread...hreadid=262734
.
--
R-e-t-u-r-n A-d-d-r-e-s-s I-n-s-t-r-u-c-t-i-o-n-s
Change
LID to