View Single Post
  #9   Report Post  
Old January 6th 18, 06:25 PM posted to microsoft.public.excel.worksheet.functions
Claus Busch Claus Busch is offline
external usenet poster
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,669
Default Excel Macro: Type mismatch (Error 13)


Am Fri, 5 Jan 2018 14:13:57 +0000 (UTC) schrieb tb:

What I am looking for is a macro that parses each worksheet
_separately_ and for each cell that is formatted as currency ($ - U.S.
Dollars) applies the "pctChange" value specified in that worksheet.


Sub Test()
Dim wsh As Worksheet
Dim rngc As Range, c As Range
Dim factor As Double
Dim strFormat As String

strFormat = """$""#,##0.00_);(""$""#,##0.00)"

For Each wsh In Worksheets
With wsh
Set c = .UsedRange.Find("pctChange", LookIn:=xlValues)
If Not c Is Nothing Then factor = 1 + c.Offset(1, 0)
If c Is Nothing Or factor = 1 Then GoTo Skip
For Each rngc In .UsedRange
If rngc.NumberFormat = strFormat And IsNumeric(rngc) Then
rngc = Round(rngc * factor, 2)
End If
End With
End Sub

Claus B.
Office 2016