Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old January 8th 18, 06:12 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 75
Default Excel Macro: Type mismatch (Error 13)

On 1/6/2018 at 12:25:03 PM Claus Busch wrote:

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.


Thanks. I will test your code and see what happens.

--
tb

  #12   Report Post  
Old January 8th 18, 08:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 945
Default Excel Macro: Type mismatch (Error 13)

On 1/6/2018 at 7:56:51 AM GS wrote:

Oops.., didn't fully cleanup after testing! The working code
follows...

Sub RoundCurrencyValues()
Dim 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 ActiveSheet.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


Thanks I will try the code out. I'm having problems understanding how
to declare "pctChange" with local scope. I've never created a macro
myself and cannot find an example of such declaration with Google.

The best example that I could find about scope declaration is this:
<http://www.cpearson.com/excel/scope.aspx
but it does not seem to address the local scope...


On the *Formulas* tab, use *Name Manager* and set the scope to the %cell on the
sheet that's active. Activate the next sheet and select its %cell and repeat.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #13   Report Post  
Old January 17th 18, 11:05 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 75
Default Excel Macro: Type mismatch (Error 13)

On 1/8/2018 at 2:10:26 PM GS wrote:

On 1/6/2018 at 7:56:51 AM GS wrote:

Oops.., didn't fully cleanup after testing! The working code
follows...

Sub RoundCurrencyValues()
Dim 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 ActiveSheet.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


Thanks I will try the code out. I'm having problems understanding
how to declare "pctChange" with local scope. I've never created a
macro myself and cannot find an example of such declaration with
Google.

The best example that I could find about scope declaration is this:
<http://www.cpearson.com/excel/scope.aspx
but it does not seem to address the local scope...


On the Formulas tab, use *Name Manager* and set the scope to the
%cell on the sheet that's active. Activate the next sheet and select
its %cell and repeat.


Garry:

Your macro seems to work fine. The only problem that I've found is
that it puts a "$0.00" in every blank cell that is formatted as
currency. If a cell is blank (and is formatted as currency), I'd like
it to stay so.

Below is the complete macro as you created it for me. Would you mind
seeing if it can be modified as per the above requirement? Also why is
there a "$" character in constant "sCurrencyFormat$"?

*****
Option Explicit

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

Sub RoundCurrencyValues()
Dim 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 ActiveSheet.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
*****

Thanks.
--
tb
  #14   Report Post  
Old January 18th 18, 05:18 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 945
Default Excel Macro: Type mismatch (Error 13)

Also why is there a "$" character in constant "sCurrencyFormat$"?

The Option Explicit statement enforces variable declaration and VBA 'best
practice' is to turn this on in the 'General' tab of the VBE Preferences
dialog.

The NumberFormat definitions are passed as String values. The $ character is
the Type symbol for 'String'. I have Lou Gehrig's and so I like to save typing
when possible; -using the VB type symbols helps with that!

The alternate syntax is below the line (as a comment)...

*****
Option Explicit

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

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

Sub RoundCurrencyValues()
Dim 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 ActiveSheet.UsedRange.Cells


With crng
'If cell number format is Currency AND cell not empty
If .NumberFormat = sCurrencyFormat And Len(crng) 0 Then _
crng.Value = WorksheetFunction.Round(crng * (1 + rng), 2)
End With 'crng

Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub
*****

Thanks.


--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #15   Report Post  
Old February 6th 18, 08:58 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 75
Default Excel Macro: Type mismatch (Error 13)

On 1/6/2018 at 7:56:51 AM GS wrote:

Oops.., didn't fully cleanup after testing! The working code
follows...

Sub RoundCurrencyValues()
Dim 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 ActiveSheet.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


Your macro worked just fine. Thanks, Garry!

--
tb


  #16   Report Post  
Old February 6th 18, 09:04 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 75
Default Excel Macro: Type mismatch (Error 13)

On 1/6/2018 at 12:25:03 PM Claus Busch wrote:

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.


Claus, I was not able to use your macro. Basically, nothing happes
when I launch it. Is there something else that I need to do in order
to successfully run your macro?

Garry's macro worked for me, but I am interested in learning more about
macros and maybe I'm doing something wrong with yours...

--
tb
  #17   Report Post  
Old February 7th 18, 10:40 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 945
Default Excel Macro: Type mismatch (Error 13)

On 1/6/2018 at 7:56:51 AM GS wrote:

Oops.., didn't fully cleanup after testing! The working code
follows...

Sub RoundCurrencyValues()
Dim 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 ActiveSheet.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


Your macro worked just fine. Thanks, Garry!


You're welcome; -glad to help and I appreciate the feedback!

--
Garry

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
runtime error 13 - type mismatch error in Excel 97 on Citrix Kevin Maher Excel Programming 7 March 8th 08 11:48 AM
Error type mismatch in writing macro ub Excel Worksheet Functions 2 September 26th 07 12:48 PM
Macro error type mismatch Jurassien Excel Discussion (Misc queries) 3 February 23rd 07 08:14 PM
type mismatch error in Benford's law macro Sody Excel Programming 1 May 24th 06 11:07 PM
Visual Basic macro run time error(13) type mismatch Paul Excel Discussion (Misc queries) 0 October 25th 05 07:28 AM


All times are GMT +1. The time now is 12:16 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017