infinitemile
01-07-2018, 06:08 PM
Hello everyone, this is my first time here. I've new to Excel VBA, yet I'm not new to coding in general; I've had experience with Java.
Anyways, I've been trying to develop this code to help me in a personal study. The goal of the code is to take a series of probabilities (e.g. 0.125) and change that probability based on text in the neighbouring cell. For example:
Column 1
Column 2
95
=0.1 * (1-.02)
96
= (1-.01) * 0.2
95, 96
= 0.1 * 0.2
In this case, the probability of 95 is .1, and 96 is .2.
The actual data in question is a series of two numbers on the left, followed by 13 probabilities on the right that change as shown above. Here is my code:
Private Sub CommandButton1_Click()
Dim x As Double
x = (1 - 0.188) * (1 - 0.142) * (1 - 0.081) * (1 - 0.142) * (1 - 0.142) * (1 - 0.482) * (1 - 0.107) * (1 - 0.482) * (1 - 0.081) * (1 - 0.107) * (1 - 0.107) * (1 - 0.081) * (1 - 0.018)
Dim a As String
Range("B1:B156").Value = x
Dim ProbChoice As String
ProbChoice = ""
For i = 1 To 156
a = Cells(i, 1).Value
If InStr(1, a, "95") > 0 Then
ProbChoice = ProbChoice & "A"
End If
If InStr(a, "96") > 0 Then
ProbChoice = ProbChoice & "B"
End If
If InStr(a, "97") > 0 Then
ProbChoice = ProbChoice & "C"
End If
If InStr(a, "99") > 0 Then
ProbChoice = ProbChoice & "D"
End If
If InStr(a, "00") > 0 Then
ProbChoice = ProbChoice & "E"
End If
If InStr(a, "01") > 0 Then
ProbChoice = ProbChoice & "F"
End If
If InStr(a, "08") > 0 Then
ProbChoice = ProbChoice & "G"
End If
If InStr(a, "09") > 0 Then
ProbChoice = ProbChoice & "H"
End If
If InStr(a, "10") > 0 Then
ProbChoice = ProbChoice & "I"
End If
If InStr(a, "11") > 0 Then
ProbChoice = ProbChoice & "J"
End If
If InStr(a, "12") > 0 Then
ProbChoice = ProbChoice & "K"
End If
If InStr(a, "14") > 0 Then
ProbChoice = ProbChoice & "L"
End If
If InStr(a, "17") > 0 Then
ProbChoice = ProbChoice & "M"
End If
Select Case ProbChoice
Case InStr(1, ProbChoice, "A") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.188) / (1 - 0.188)
Case InStr(ProbChoice, "B") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.142) / (1 - 0.142)
Case InStr(ProbChoice, "C") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.081) / (1 - 0.081)
Case InStr(ProbChoice, "D") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.142) / (1 - 0.142)
Case InStr(ProbChoice, "E") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.142) / (1 - 0.142)
Case InStr(ProbChoice, "F") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.482) / (1 - 0.482)
Case InStr(ProbChoice, "G") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.107) / (1 - 0.107)
Case InStr(ProbChoice, "H") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.482) / (1 - 0.482)
Case InStr(ProbChoice, "I") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.081) / (1 - 0.081)
Case InStr(ProbChoice, "J") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.107) / (1 - 0.107)
Case InStr(ProbChoice, "K") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.107) / (1 - 0.107)
Case InStr(ProbChoice, "L") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.081) / (1 - 0.081)
Case InStr(ProbChoice, "M") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.018) / (1 - 0.018)
End Select
Next i
End Sub
I'm not sure what's wrong, but the switch case part isn't working right. Right now it fills all the cells in with the same x probability and doesn't change it at all. Any help would be welcome :)
Thanks!
Anyways, I've been trying to develop this code to help me in a personal study. The goal of the code is to take a series of probabilities (e.g. 0.125) and change that probability based on text in the neighbouring cell. For example:
Column 1
Column 2
95
=0.1 * (1-.02)
96
= (1-.01) * 0.2
95, 96
= 0.1 * 0.2
In this case, the probability of 95 is .1, and 96 is .2.
The actual data in question is a series of two numbers on the left, followed by 13 probabilities on the right that change as shown above. Here is my code:
Private Sub CommandButton1_Click()
Dim x As Double
x = (1 - 0.188) * (1 - 0.142) * (1 - 0.081) * (1 - 0.142) * (1 - 0.142) * (1 - 0.482) * (1 - 0.107) * (1 - 0.482) * (1 - 0.081) * (1 - 0.107) * (1 - 0.107) * (1 - 0.081) * (1 - 0.018)
Dim a As String
Range("B1:B156").Value = x
Dim ProbChoice As String
ProbChoice = ""
For i = 1 To 156
a = Cells(i, 1).Value
If InStr(1, a, "95") > 0 Then
ProbChoice = ProbChoice & "A"
End If
If InStr(a, "96") > 0 Then
ProbChoice = ProbChoice & "B"
End If
If InStr(a, "97") > 0 Then
ProbChoice = ProbChoice & "C"
End If
If InStr(a, "99") > 0 Then
ProbChoice = ProbChoice & "D"
End If
If InStr(a, "00") > 0 Then
ProbChoice = ProbChoice & "E"
End If
If InStr(a, "01") > 0 Then
ProbChoice = ProbChoice & "F"
End If
If InStr(a, "08") > 0 Then
ProbChoice = ProbChoice & "G"
End If
If InStr(a, "09") > 0 Then
ProbChoice = ProbChoice & "H"
End If
If InStr(a, "10") > 0 Then
ProbChoice = ProbChoice & "I"
End If
If InStr(a, "11") > 0 Then
ProbChoice = ProbChoice & "J"
End If
If InStr(a, "12") > 0 Then
ProbChoice = ProbChoice & "K"
End If
If InStr(a, "14") > 0 Then
ProbChoice = ProbChoice & "L"
End If
If InStr(a, "17") > 0 Then
ProbChoice = ProbChoice & "M"
End If
Select Case ProbChoice
Case InStr(1, ProbChoice, "A") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.188) / (1 - 0.188)
Case InStr(ProbChoice, "B") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.142) / (1 - 0.142)
Case InStr(ProbChoice, "C") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.081) / (1 - 0.081)
Case InStr(ProbChoice, "D") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.142) / (1 - 0.142)
Case InStr(ProbChoice, "E") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.142) / (1 - 0.142)
Case InStr(ProbChoice, "F") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.482) / (1 - 0.482)
Case InStr(ProbChoice, "G") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.107) / (1 - 0.107)
Case InStr(ProbChoice, "H") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.482) / (1 - 0.482)
Case InStr(ProbChoice, "I") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.081) / (1 - 0.081)
Case InStr(ProbChoice, "J") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.107) / (1 - 0.107)
Case InStr(ProbChoice, "K") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.107) / (1 - 0.107)
Case InStr(ProbChoice, "L") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.081) / (1 - 0.081)
Case InStr(ProbChoice, "M") > 0
Cells(i, 2).Value = (Cells(i, 2).Value * 0.018) / (1 - 0.018)
End Select
Next i
End Sub
I'm not sure what's wrong, but the switch case part isn't working right. Right now it fills all the cells in with the same x probability and doesn't change it at all. Any help would be welcome :)
Thanks!