# VBA - translating excel formula to calclulate how many million in big number

#### johnloco

##### New Member
Hello,

first I must say that I'm impressed and motivated by people on this forum to explore excel and to learn a few tricks... so thank you.

Im trying to create VBA Userform to replace my excel calculator for some home business. And I'm stuck with following:

How to create formula that will calculate number of millions in big multimillion number? I was using Rounddown in excel, but since Rounddown is not optional in VBA, I'm asking for help.

What i want to achieve is to calculate value based on input number, in case that up to 7.000.000 value is 50 but for any value above 7.000.000 final value has to be calculated based on 50 +(1 for each starting million). For example: input of 7.000.000 gives 50, input of 8.954.000 gives 51, input of 11.010.000 is 54 etc...

I would appreciate any idea... sorry if my explanation is a bit confusing, English is not my primary language...

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
rounddown is available in vba. In fact, all functions (I think....) are available in vba. You just have to access them slightly differently.

Code:
``application.WorksheetFunction.RoundDown(arg1, arg2)``

Neon, tnax for quick reply... i'm still learing VBA, i'm trying to replace this formula :
IF(F8<=385000000;(280+(ROUNDDOWN(((F8-13000000)/1000000);0)*10)

or if you have other idea hot to achieve a.m.

My code - i guess it will better explain my intention:

Private Sub CommandButton3_Click()

TextBox12.Value = "xxxxxxxxxxxxxxxxxxxxxx"

If TextBox8 <= 600000 Then
TextBox11.Value = 60

ElseIf TextBox8 <= 2500000 Then
TextBox11.Value = 100

ElseIf TextBox8 <= 5000000 Then
TextBox11.Value = 140

ElseIf TextBox8 <= 7000000 Then
TextBox11.Value = 200

ElseIf TextBox8 <= 10000000 Then
TextBox11.Value = 240

ElseIf TextBox8 <= 14000000 Then
TextBox11.Value = 280

ElseIf TextBox8 <= 385000000 Then
"i want to be able to calculate the following - in case of number higher than 14m and smaller than 385m, value is 280+(10*each starting million until 385m)"

Else

TextBox11.Value = 4000

End If

End Sub

Neon, tnax for quick reply... i'm still learing VBA, i'm trying to replace this formula :
IF(F8<=385000000;(280+(ROUNDDOWN(((F8-13000000)/1000000);0)*10)

or if you have other idea hot to achieve a.m.

Code:
``````Private Sub CommandButton3_Click()
TextBox12.Value = "xxxxxxxxxxxxxxxxxxxxxx"

With TextBox11
Select Case TextBox8.Value
Case Is <= 600000
.Value = 60
Case Is <= 2500000
.Value = 100
Case Is <= 7000000
.Value = 140
Case Is <= 10000000
.Value = 200
Case Is <= 14000000
.Value = 240
Case Is <= 385000000
.Value = 280 + (Application.WorksheetFunction.RoundDown((( _
Range("F8") - 13000000) / 1000000), 0) * 10)
Case Else
.Value = 4000
End Select
End With``````

Try this.

works fine for values below 14.000.000...not working for input values above 14.000.000... Range ("F8") - what this is refering to?

Code:
``````Private Sub CommandButton3_Click()
TextBox12.Value = "xxxxxxxxxxxxxxxxxxxxxx"

With TextBox11
Select Case TextBox8.Value
Case Is <= 600000
.Value = 60
Case Is <= 2500000
.Value = 100
Case Is <= 7000000
.Value = 140
Case Is <= 10000000
.Value = 200
Case Is <= 14000000
.Value = 240
Case Is <= 385000000
.Value = 280 + (Application.WorksheetFunction.RoundDown((( _
Range("F8") - 13000000) / 1000000), 0) * 10)
Case Else
.Value = 4000
End Select
End With``````

Try this.

F8 is referring to your formula that you posted.

Code:
``[COLOR=#333333][I]IF(F8<=385000000;(280+(ROUNDDOWN(((F8-13000000)/1000000);0)*10)[/I][/COLOR]``

Ok...in userform f8 is replaced by textbox8 value...can you pls make ajdustment?
F8 is referring to your formula that you posted.

Code:
``[COLOR=#333333][I]IF(F8<=385000000;(280+(ROUNDDOWN(((F8-13000000)/1000000);0)*10)[/I][/COLOR]``

Code:
``````Private Sub CommandButton3_Click()TextBox12.Value = "xxxxxxxxxxxxxxxxxxxxxx"

With TextBox11
Select Case TextBox8.Value
Case Is <= 600000
.Value = 60
Case Is <= 2500000
.Value = 100
Case Is <= 7000000
.Value = 140
Case Is <= 10000000
.Value = 200
Case Is <= 14000000
.Value = 240
Case Is <= 385000000
.Value = 280 + (Application.WorksheetFunction.RoundDown((( _
TextBox8.Value - 13000000) / 1000000), 0) * 10)
Case Else
.Value = 4000
End Select
End With

End Sub``````

Works perfectly! Thank you very much, Neon...

Code:
``````Private Sub CommandButton3_Click()TextBox12.Value = "xxxxxxxxxxxxxxxxxxxxxx"

With TextBox11
Select Case TextBox8.Value
Case Is <= 600000
.Value = 60
Case Is <= 2500000
.Value = 100
Case Is <= 7000000
.Value = 140
Case Is <= 10000000
.Value = 200
Case Is <= 14000000
.Value = 240
Case Is <= 385000000
.Value = 280 + (Application.WorksheetFunction.RoundDown((( _
TextBox8.Value - 13000000) / 1000000), 0) * 10)
Case Else
.Value = 4000
End Select
End With

End Sub``````

Replies
7
Views
400
Replies
9
Views
588
Replies
12
Views
310
Replies
5
Views
109
Replies
2
Views
368

1,219,970
Messages
6,151,213
Members
451,017
Latest member
peterlam84

### 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.

### Which adblocker are you using?

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

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