Can anyone crack this VBA formula???

Ben S

Board Regular
Joined
Oct 21, 2002
Messages
124
How would i write the following calculation in VBA. I have a form set up and would like textbox10 to calculate the following.

If ComboBox1 ="Support or Police" and ComBoBox2 ="Business"
Then
=(((ComboBox3.Value/12*4.085%*TextBox4.Value)+(TextBox4.Value))/(ComboBox3.value))
If ComboBox1 ="Support or Police" and ComBoBox2 ="Standard"
Then
=(((ComboBox3.Value/12*4.585%*TextBox4.Value)+(TextBox4.Value))/(ComboBox3.value))
If ComboBox1 ="Support or Police" and ComBoBox2 ="Standard £15K"
Then
=(((ComboBox3.Value/12*4.43%*TextBox4.Value)+(TextBox4.Value))/(ComboBox3.value))

I also need to display to two decimal places and use the £ sign
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
Hi,

untested but should work (fingers crossed):

Sub Tester()

If Combobox1 = "Support" Or "Police" Then
Select Case Combobox2
Case "Business"
TextBox10 = ((ComboBox3.Value / 12 * .004085 * TextBox4.Value) + (TextBox4.Value)) / (ComboBox3.Value)
Case "Standard"
TextBox10 = ((ComboBox3.Value / 12 * 0.004585 * TextBox4.Value) + (TextBox4.Value)) / (ComboBox3.Value)
Case "Standard £15K"
TextBox10 = ((ComboBox3.Value / 12 * 0.00443 * TextBox4.Value) + (TextBox4.Value)) / (ComboBox3.Value)
End Select
End If

End Sub

HTH,
Corticus
This message was edited by Corticus on 2002-10-30 10:30
 

Ben S

Board Regular
Joined
Oct 21, 2002
Messages
124
Does not produce any figures. Is there anything else i have to do
This is the code i have input. Do i have to place any code on other fields

Sub Tester()

If ComboBox1 = "Support" Or "Police" Then
Select Case ComboBox2
Case "Business"
TextBox10 = ((ComboBox3.Value / 12 * 0.004085 * TextBox4.Value) + (TextBox4.Value)) / (ComboBox3.Value)
Case "Standard"
TextBox10 = ((ComboBox3.Value / 12 * 0.004585 * TextBox4.Value) + (TextBox4.Value)) / (ComboBox3.Value)
Case "Standard £15K"
TextBox10 = ((ComboBox3.Value / 12 * 0.00443 * TextBox4.Value) + (TextBox4.Value)) / (ComboBox3.Value)
End Select
End If

End Sub
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
I trust you mean a Userform.

Does that code work? What's the default property of a listbox?? Okay, I just looked, it's Value. Value returns what the ListIndex of BoundColumn contains. Maybe you don't have the proper BoundColumn?


Normally, I would explicitly test .List(row,colum), perhaps Trim()ing the result before testing it against "TestString", and perhaps lCase()ing or uCase()ing both items.

OR, if you hard-code the strings into the listbox, you might just want to test for .listindex (if not a multi-select).

If you are dealing with multi-select, then you need to test the .selected property (boolean).
 

Forum statistics

Threads
1,144,148
Messages
5,722,784
Members
422,457
Latest member
Mrmuskins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top