Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Audit function needed
In a multi-currency environment my staff keep mixing up currencies in
their spreadsheets e.g. subtracting costs in dollars from revenues in Euros. I would like to develop a macro that examines formulas in a spreadsheet and looks for cells formatted in different currencies. Some pointers on how to get started would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Audit function needed
Don't know your formats, so I will use a format of £#,##0.00;(£#,##0.00) Dim cell As Range Dim rng As Range For Each cell In Selection If Not IsEmpty(cell) Then If IsNumeric(cell.Value) Then If cell.NumberFormat < "£#,##0.00;(£#,##0.00)" Then If rng Is Nothing Then Set rng = cell Else Set rng = Union(rng, cell) End If End If End If End If Next cell If Not rng Is Nothing Then rng.Select End If -- HTH Bob Phillips "bvm" wrote in message ... In a multi-currency environment my staff keep mixing up currencies in their spreadsheets e.g. subtracting costs in dollars from revenues in Euros. I would like to develop a macro that examines formulas in a spreadsheet and looks for cells formatted in different currencies. Some pointers on how to get started would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Audit function needed
that would not (easily) happen in a well designed sheet. make sure that different currencies end up in different columns. Or: have 1 column for "values" and a separate column as a currency selector. (3rd column ofcourse the calculated value in "reporting currency" never rely on numberformatting... (what if they paste values?) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam bvm wrote : In a multi-currency environment my staff keep mixing up currencies in their spreadsheets e.g. subtracting costs in dollars from revenues in Euros. I would like to develop a macro that examines formulas in a spreadsheet and looks for cells formatted in different currencies. Some pointers on how to get started would be appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Audit function needed
bvm wrote:
In a multi-currency environment my staff keep mixing up currencies in their spreadsheets e.g. subtracting costs in dollars from revenues in Euros. I would like to develop a macro that examines formulas in a spreadsheet and looks for cells formatted in different currencies. Some pointers on how to get started would be appreciated. Thanks Bob that helped and by combining your suggestions with the Precedent function I have been able to do exactly what I wanted as stated originally. I appreciate the sentiment in keepITcool - however if everyone drove their cars properly there would not be any road accidents, but in the real world.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question on Formula Audit function | Excel Discussion (Misc queries) | |||
Help with time audit | Excel Discussion (Misc queries) | |||
Audit Trail | Excel Discussion (Misc queries) | |||
need help have audit and i'm going to get slaughtered unless | Excel Worksheet Functions | |||
Add "FIND LINKS" to EXCEL Audit function | Excel Worksheet Functions |