Using VBA to do this?
 

Hi everyone,

Say I am running Excel optimizer to solve a model 10 times. Say at run
1, the solution array (it is not one value solution) is something like
this:

Cost Volume Weight Cooling
2.3 5.4 4.1 7.1

At run 2, I want to check if the 2nd solution array is same as 1st,
then don't write it down and go to the 3rd run.

At run 3, I want to check if the 3rd solution array is same as 1st OR
2nd, then don't write it down and go to the 4th run.

And so on.

How can I do so using VBA?

Thanks,
Mike

Using VBA to do this?
 

Change 12 to 1 greater than the most answer rows you could have.

Dim rng as Range, cell as Range, cell1 as Range
Dim i as long, cnt as Long
Dim bDup as Boolean
if Not isempty(cells(1,1)) then
set rng = Range(cells(1,1),Cells(12,1).End(xlup))
for each cell in rng
i = 0
cnt = 0
bDup = False
for each cell1 in cell.Resize(1,4)
if cell1.Value = solutionArray(i) then
cnt = cnt + 1
end if
i = i + 1
Next
if cnt = 4 then
bDup = True
exit for
end if
Next
End if
if not bDup then
' write array

--
Regards,
Tom Ogilvy

"Michael" wrote in message
om...
Hi everyone,

Say I am running Excel optimizer to solve a model 10 times. Say at run
1, the solution array (it is not one value solution) is something like
this:

Cost Volume Weight Cooling
2.3 5.4 4.1 7.1

At run 2, I want to check if the 2nd solution array is same as 1st,
then don't write it down and go to the 3rd run.

At run 3, I want to check if the 3rd solution array is same as 1st OR
2nd, then don't write it down and go to the 4th run.

And so on.

How can I do so using VBA?

Thanks,
Mike

Using VBA to do this?
 

Tom,

Thanks alot, however what did you mean by "Change 12 to 1 greater than
the most.....etc."

I used your code but still not working so I want to make sure I
understand what did you wrote...

Regards,
Mike

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Using VBA to do this?
 

This generates 1000 4 element arrays. Each element can be a 1 or a 2. So
we know there are only 16 unique arrays.

I have used 1001 to replace the 12

Sub Tester5()
Dim rng As Range, cell As Range, cell1 As Range
Dim i As Long, cnt As Long
Dim bDup As Boolean
Dim j As Long, ii As Long
Dim solutionArray(0 To 3)
For ii = 1 To 1000
For j = 0 To 3
If Rnd() < 0.5 Then
solutionArray(j) = 1
Else
solutionArray(j) = 2
End If
Next

If Not IsEmpty(Cells(1, 1)) Then
Set rng = Range(Cells(1, 1), Cells(1001, 1).End(xlUp))
For Each cell In rng
i = 0
cnt = 0
bDup = False
For Each cell1 In cell.Resize(1, 4)
If cell1.Value = solutionArray(i) Then
cnt = cnt + 1
End If
i = i + 1
Next
If cnt = 4 Then
bDup = True
Exit For
End If
Next
End If
If Not bDup Then
If IsEmpty(Cells(1, 1)) Then
Cells(1, 1).Resize(1, 4).Value = solutionArray
Else
Cells(1001, 1).End(xlUp)(2).Resize(1, 4) = solutionArray
End If
End If
Next
End Sub

This is what I meant - since I assume you don't know how many unique
combinations you could have, you would use the number of tests you will run
+ 1. In the above example, I could use 17 rather than 1001 since I know
there will only be 16 unique possibilities.

--
Regards,
Tom Ogilvy

"Michael Sultan" wrote in message
...


Tom,

Thanks alot, however what did you mean by "Change 12 to 1 greater than
the most.....etc."

I used your code but still not working so I want to make sure I
understand what did you wrote...

Regards,
Mike

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!