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.

Step1
Make this change in your Tools==Options dialog:

Place a check in "Require Variable Declaration"


Step2
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

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion