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

johnloco

New Member
Joined
Sep 8, 2014
Messages
6
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...:biggrin:
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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)
 
Upvote 0
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)
please advice if possible?

or if you have other idea hot to achieve a.m.
 
Upvote 0
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)
please advice if possible?

or if you have other idea hot to achieve a.m.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
Works perfectly! Thank you very much, Neon... (y)

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
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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
Back
Top