How Do I Quickly Re-state Values with VBA?

Being a Reporting Analyst for a huge enterprise often means that there might be no single centralised data warehouse where all enterprise data resides but rather a number of business intelligence systems, each of which tackles a different set of business operations data. Thus, you get one system for your sales analysis, another for P&L reporting, and yet a third one for all supply chain data.

Yet, should you happen to be the analyst supporting the regional management, it is highly likely that you might have to show a number of sales, financial and supply chain KPIs in the same high-level overview scorecard.

And that’s where challenge # 1 ensues: measure units between systems are not the same and the only tool you have to address this is Excel. Of course you can tackle the problem by using some extra formulas that will divide (or multiply) as neeeded.

However, it’s much better for the efficiency of your spreadsheet if you have this as a step in your data prep model

Have you ever had to combine data where numbers were stated in different measure units – e.g. actuals in millions and budgets in thousands?

• Did you ever have to convert expenses to negative numbers?

If yes, you might find the VBA snippet below a handy trick to have up your sleeve.

Sub MultiplyValues()
Dim Cell As Range
Dim prv As Double
'written by Angelina Teneva, September 2016
'multiplies by a 1000
For Each Cell In ActiveSheet.Range("J2:J" & ActiveSheet.UsedRange.Rows.Count).SpecialCells(xlCellTypeVisible)
prv = Cell.Value
Cell.Value = prv * 1000 '*(-1)
Next Cell
'make values negative
For Each Cell In ActiveSheet.Range("AV:AV", "AX:AX")
If IsNumeric(Cell) And Cell.Value <> 0 Then
prv = Cell.Value
Cell.Formula = prv * -1
End If
Next Cell
End Sub

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.