Switch case not giving desired product

[infinitemile]

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!

[yujin]

Hello, infinitemile. I'm also new to this forum, but I have years of experience with Excel VBA.
I think you need to correct 2 points out of your code.
First, string variable "ProbChoice" should be initialized inside and at the beginning of the FOR...NEXT statement.
Like this:

For i = 1 To 156
ProbChoice = ""
'following code
Next i

Second, when you use InStr() function as conditions of the CASE statement, you should write like

Select Case True
instead of

Select Case ProbChoice

I hope these can be any of your help.:)

[infinitemile]

Hello, infinitemile. I'm also new to this forum, but I have years of experience with Excel VBA.
I think you need to correct 2 points out of your code.
First, string variable "ProbChoice" should be initialized inside and at the beginning of the FOR...NEXT statement.
Like this:

For i = 1 To 156
ProbChoice = ""
'following code
Next i

Second, when you use InStr() function as conditions of the CASE statement, you should write like

Select Case True
instead of

Select Case ProbChoice

I hope these can be any of your help.:)

Hi, thanks so much! It's almost fixed. With your helpful changes to the code it seems to only be going through the String once.

For instance, this is what I'm seeing now:



Column String
Column Double


95, 96
.0173


95, 01
.0173



The doubles should actually be .003 and .016, but the code is only searching through the Switch Case once it seems? How can I make it search through again?

Thanks!

[snb]

Sub M_snb()
sn=range("A1:A156")
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)

for j=1 to ubound(sn)
y=choose(instr("959697990001081911121417",sn(j,1))\2+1,.188,.142,.181,.142,.142,.482,.107,.482,.081,.107,.107,.081,. 018)
sn(j,1)=x*y/(1-y)
next

range("B1:B156")=sn
End sub

[infinitemile]

Hi there, thanks.

This code doesn't provide the right calculations. It prints every number as .0173.

[snb]

So why not amending the code ?
You have my permission.

[yujin]

I think I've figured out what you want.
Try this:


Private Sub CommandButton1_Click()
Dim x As Double
Dim i As Integer
Dim a As String

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)
Range("B1:B156").Value = x


For i = 1 To 156
a = Cells(i, 1).Value

If InStr(1, a, "95") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.188) / (1 - 0.188)
End If
If InStr(a, "96") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.142) / (1 - 0.142)
End If
If InStr(a, "97") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.081) / (1 - 0.081)
End If
If InStr(a, "99") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.142) / (1 - 0.142)
End If
If InStr(a, "00") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.142) / (1 - 0.142)
End If
If InStr(a, "01") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.482) / (1 - 0.482)
End If
If InStr(a, "08") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.107) / (1 - 0.107)
End If
If InStr(a, "09") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.482) / (1 - 0.482)
End If
If InStr(a, "10") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.081) / (1 - 0.081)
End If
If InStr(a, "11") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.107) / (1 - 0.107)
End If
If InStr(a, "12") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.107) / (1 - 0.107)
End If
If InStr(a, "14") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.081) / (1 - 0.081)
End If
If InStr(a, "17") > 0 Then
Cells(i, 2).Value = (Cells(i, 2).Value * 0.018) / (1 - 0.018)
End If
Next i
End Sub

[infinitemile]

thanks so much! I actually developed code that works just as I saw yours, but I tried both out and they both work. :rofl: thanks guys!