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.