Re: Large worksheet formatting: change % formats only?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bill Weylock (bill_at_nospam.net)
Date: 01/05/05 

Date: Wed, 05 Jan 2005 08:52:59 -0800

That is so great.

Hope I can do something for you sometime. Considering the gap in expertise
... Need your sock drawer arranged?

Thanks so much!

Best,

 - Bill

On 1/5/05 3:14 AM, in article BE013648.5CF5%j.laro@NsOySmPpAaMtico.ca, "J
Laroche" <j.laro@NsOySmPpAaMtico.ca> wrote:

> The macro stopped on a cell in which 100.00% was directly typed instead of
> being the result of a formula. You can bypass the problem with this new
> version, which will ignore those cells without a formula. However if the
> cellıs formula is =1 (or any other value with the 0.00% format), the result
> will be correct but left-aligned, so you can spot it easily.
>
> Sub convert_percent()
> d = Application.International(xlDecimalSeparator)
> For Each c In ActiveSheet.UsedRange.Cells
> If c.NumberFormat = "0.00%" And Application.IsNumber(c.Value) Then
> f = Right(c.Formula, Len(c.Formula) - 1)
> If f <> "" Then
> c.Formula = "=TEXT(" & f & ",LEFT(""0" & d & _
>>> "00"",1 + 2*(MOD(ROUND(" & f & _
>>> "*100,2),1)>0) + (MOD(ROUND(" & f & _
>>> "*1000,1),1)>0)) & ""%"")"
>>> c.NumberFormat = "General"
> End If
> End If
> Next
> End Sub
>
>
>
> JL
> Mac OS X 10.3.7, Office v.X 10.1.6
>
>
> Bill Weylock wrote on 2005/01/04 22:48:
>
>> Thanks so much.
>>
>> That is exactly what I needed!
>>
>> Only thing is that I am a stumblebum novice at using macros. I may have
>> screwed something up, or there may be a tiny error somewhere in the macro.
>>
>> I created a VBA module in the workbook, pasted your code into it, and saved
>> it.
>>
>> When I started to run it, things hummed along for a few seconds, but then I
>> got an error:
>>
>> Run-time error Œ1004ı
>>
>> Application-defined or object-defined error.
>>
>> I clicked the Debug button and selected ³Step through².
>>
>> The four lines I have just colored red below were in white against a red
>> background.
>>
>> I read the help message, but itıs over my head.
>>
>> When I looked at the worksheet, most of the first table had been reformatted
>> perfectly. Looks as if it worked fine until it encountered 100.00%. Could
>> that be the problem? Wasnıt looking for three digits to the left of the
>> decimal?
>>
>> Thanks so much again!
>>
>>
>> Best,
>>
>>
>> - Bill
>>
>>
>> On 1/4/05 7:14 PM, in article BE00C5B7.5CE9%j.laro@NsOySmPpAaMtico.ca, "J
>> Laroche" <j.laro@NsOySmPpAaMtico.ca> wrote:
>>
>>> JE McGimpsey wrote on 2005/01/04 15:01:
>>>
>>>> > In article <BE003363.20BA5%bill@nospam.net>,
>>>> > Bill Weylock <bill@nospam.net> wrote:
>>>> >
>>>>> >> The formula results are exactly what I want. When I try to apply it,
>>>>> >> however, it produces a cell not formatted as a percent. I can get the
>>>>> result
>>>>> >> I want only by manually applying a % format with the number of places I
>>>>> >> need.
>>>> >
>>>> >
>>>> > Whoops - you're exactly right - I'd applied one of the custom formats
>>>> > first. Remove the VALUE(...) wrapper. It produces text, but can be
>>>> > coerced to values if needed in subsequent calculations.
>>>
>>>
>>> Bill, since you have a very large number of cells to reformat (or
>>> reformulate), a macro can do the job for you. Copy the code below in a
>>> module, then when the sheet to reformat is the active one execute the macro.
>>> The cells will still contain their original formula, so even if source data
>>> is changed data will stay up-to-date.
>>>
>>>
>>> Sub convert_percent()
>>> d = Application.International(xlDecimalSeparator)
>>> For Each c In ActiveSheet.UsedRange
>>> If c.NumberFormat = "0.00%" And Application.IsNumber(c.Value) Then
>>> f = Right(c.Formula, Len(c.Formula) - 1)
>>> c.Formula = "=TEXT(" & f & ",LEFT(""0" & d & _
>>> "00"",1 + 2*(MOD(ROUND(" & f & _
>>> "*100,2),1)>0) + (MOD(ROUND(" & f & _
>>> "*1000,1),1)>0)) & ""%"")"
>>> c.NumberFormat = "General"
>>> End If
>>> Next
>>> End Sub
>>>
>>>
>>> JL
>>> Mac OS X 10.3.7, Office v.X 10.1.6
>>>
>>
>>
>>
>>
>> Panther 10.3.6
>> Office 2004
>> Windows XP Pro SP2
>> Office 2003
>>
>>
>
>

Panther 10.3.6
Office 2004
Windows XP Pro SP2
Office 2003

Relevant Pages

  • Re: Large worksheet formatting: change % formats only?
    ... > End Sub ... >> Only thing is that I am a stumblebum novice at using macros. ... >> Windows XP Pro SP2 ...
    (microsoft.public.mac.office.excel)
  • Help with CommandButton vs Excel 2002
    ... Windows machine to a Mac. ... After opening the workbook on the Mac I ... found that none of the macros seemed to work. ... Sub MyCheck_Click ...
    (microsoft.public.excel.programming)
  • Re: How to capture Max cell value (High Water Mark)
    ... Where A1 is the cell that is changing and the HiWater function can be in any ... I don't have any other macros in the workbook. ... Sub Test_Enable_Events ... I'm only using it on the same worksheet where the macro was created. ...
    (microsoft.public.excel)
  • Re: How to capture Max cell value (High Water Mark)
    ... directly addressing the changing cell. ... I don't have any other macros in the workbook. ... Sub Test_Enable_Events ... "Note that it only works with the worksheet under which it was ...
    (microsoft.public.excel)
  • Re: One more question on worksheet protection
    ... You can't see the macros because they're called with arguments. ... protect/unprotect all VBA projects (including 'Personal.xls' if it exists - so ... Sub ProtectOpenVBAProjects() ... | Dim vbProj As Object ...
    (microsoft.public.excel.programming)