View Single Post
  #9   Report Post  
Old January 6th 18, 07: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,651
Default Excel Macro: Type mismatch (Error 13)

Hi,

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.


try:

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
Next
End With
Skip:
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016