• HOME
  • ABOUT
  • BASIC EXCEL
  • VBA TIP

FUNNY EXCEL

  • HOME
  • ABOUT
  • BASIC EXCEL
  • VBA TIP
VBA CODE SHORT USD 1.000.000.000 INTO 1 BILLION-DOLLAR

VBA CODE SHORT USD 1.000.000.000 INTO 1 BILLION-DOLLAR

by mrhonghat 03/08/201905/08/2019
03/08/201905/08/2019 635 views

Idea

In fact, when making a report you may have trouble with very long data units. If you want to short it into a billion-dollar unit, you have to divide 1.000.000.000 for each cell. This work is repeated and time consuming. To solve this problem, I will share with you a small piece of code to easily convert to billion-dollar units with one click.

Topic:

Result:

Code details

First, you will create a function called Short(), which converts the value from USD to billion-dollar. That is done by dividing by 1 billion.


Function Short()

Dim c As Range

Dim myRange As Range

Set myRange = Selection

For Each c In myRange

c.Value = c.Value / 1000000000

Next c

End Function


Next, you will insert this function into the Macro named Split. Macro Split will execute the Short function for each cell in the array we selected earlier. You use the For Each Next loop to select each cell in the array. We use On Error GoTo so that Excel stops Short function when an error occurs.


Sub Split()

On Error GoTo ErrOcccered

Dim a

Dim myRange

Set myRange = Selection

For Each a In myRange

a.Value = Short(a.Value)

Next a

ErrOcccered:

0

End Sub

After, complete the code and asign it to the button. You will be like the image above.

short billion dollarvba excel
0 comment
2
FacebookTwitterGoogle +Pinterest
previous post
CREATE DATABASE ACCESS FROM EXCEL BY VBA
next post
NUMBER FORMAT TRICK WITH THOUSANDS, MILLIONS AND BILLIONS WHEN USE COMMAS TO SEPARATE THOUSANDS

Leave a Comment Cancel Reply

Save my name, email, and website in this browser for the next time I comment.

VBA TIP

  • CODE VBA TO REMOVE ALL SHAPES QUICKLY IN EXCEL
  • SHARE ADD IN HCACULATOR IN WORD
  • VBA CODE SHORT USD 1.000.000.000 INTO 1 BILLION-DOLLAR
  • CREATE DATABASE ACCESS FROM EXCEL BY VBA
  • SPLIT TEXT STRING BY DELIMITERS
  • DIR FUNCTION IN VBA EXCEL
  • WHAT IS VBA?

@2019 - funyex.com. All Right Reserved. Designed and Developed by PenciDesign


Back To Top