Latest Posts

Format Data for User Friendly Presentation

How to format data for user friendly presentation automatically with VBA.¬† When we enter item quantities the user may not know whether we are talking about the number of ‘pcs’ or ‘doz’ or ‘Kg’, etc. We can use custom formatting to display quantities with the description to make the data user-friendly. We can do the same with currency. Watch the video:

Watch this video on YouTube.

Here’s the complete VBA code to create user-friendly formatted data:

Sub formatData1()

‘ formatData1 Macro
‘ Formatting numbers as strings

Range(“I2”).Select
Selection.NumberFormat = “0 “”pcs”””
Range(“J2”).Select
Selection.NumberFormat = “0 “”INR”””
Range(“K2”).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = “=RC[-2]*RC[-1]”
Range(“K2”).Select
Selection.NumberFormat = “0 “”INR”””
Range(“J17”).Select
End Sub

Sub formatData2()
Dim i As Long, lastrow As Long
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row

For i = 2 To lastrow

Cells(i, 2).NumberFormat = “0 “”pcs”””
Cells(i, 3).NumberFormat = “0 “”INR”””

‘To place the currency before the value we can use the number-format:
‘¬†Cells(i, 3).NumberFormat =”””INR”” 0″

Cells(i, 4).Value = Cells(i, 2).Value * Cells(i, 3).Value
Cells(i, 4).NumberFormat = “0 “”INR”””
Next i

End Sub