Audit function needed

[bvm]

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.

 

[Bob Phillips]

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

 

[keepITcool]

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 :

 

[bvm]

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....