View Single Post
  #7   Report Post  
Old January 6th 18, 06:15 AM posted to microsoft.public.excel.worksheet.functions
GS[_5_] GS[_5_] is offline
external usenet poster
First recorded activity by ExcelBanter: May 2010
Posts: 226
Default Excel Macro: Type mismatch (Error 13)

After you have defined "pctChange" with local scope to each sheet the name is
used on, follow these steps to complete your project.

Make this change in your Tools==Options dialog:

Place a check in "Require Variable Declaration"

Replace everything in your code module with...

Option Explicit

Const sCurrencyFormat$ = "$#,##0.00_);($#,##0.00)"

Sub RoundCurrencyValues()
Dim wks As Worksheet, rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In wks.UsedRange.Cells
If crng.NumberFormat = sCurrencyFormat Then _
crng.Value = WorksheetFunction.Round(crng * (1 + rng), 2)
Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub


Free usenet access at
Classic VB Users Regroup!