Automate Selection

[kaholynn]

Hi,

I need to assign weights to three groups.

W1 , W2, and W3. Summation of weights will always be 1.0. How one can assign
weights with respect to a discrete level . Example if the discrete level is
0.5 then options for W1, W2, and W3 can be

1 , 0, 0
0, 1, 0
0, 0, 1
0.5, 0.5 , 0
0.5, 0, 0.5
0, 0.5, 0.5

I was wondering if we can automate the process of assigning weights for
higher discrete level. Example, 0.1 or even lesser.

My apologies if I am not clear in explaining the problem. I will appreciate
any replies. Thank you so much.

 

[Socko]

Take a look at the following vba procedure, paste it in your code
window and run it.


Sub AssignWeight()
Dim i, j, k As Double
Dim First, Second, Third As Double
Dim DiscreteLevel As Double
Dim sAssignedWeights As String
DiscreteLevel = 0.05

For i = 0 To 1 Step DiscreteLevel
For j = 0 To 1 Step DiscreteLevel
For k = 0 To 1 Step DiscreteLevel
If i + j + k = 1 Then
First = i
Second = j
Third = k
sAssignedWeights = sAssignedWeights & vbNewLine & _
First & ", " & Second & ", " & Third
End If
Next k
Next j
Next i
MsgBox sAssignedWeights
End Sub

I hope this helps

- Selva V Pasupathy
For more on VBA, Excel, & Other Programming
Please visit http://socko.wordpress.com

 

[kaholynn]

Selva,

Thank you so much. The VBA code works. Just have a question (I am new to VBA
codes). How to view the results in a worksheet (example sheet1) instead of
the message box (MsgBox). I cannot view all the assigned weights.

Many thanks.

 

[Don Guillett]

MsgBox sAssignedWeights
to
sheets("sheet1").range("a1").value=sAssignedWeights

 

[kaholynn]

Great! Thank you so much Don. Your suggestion works.

Thanks,