Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 3rd, 2011, 12:12 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

i just made some changes to the previous post and i don't know if it emails you when i edit a post, so check my previous post again
 
Old May 4th, 2011, 02:50 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

The code you mentioned which would simplify it, can that still be used in a loop?

Edit:

Also i just tried it with the if statement below and the numer decimal line works but it ignores the percentage decimal line. I tested to see if it would pick it up the first percentage line if the second percentage line was not there but it then didn't detect any percentage formatting. I also noticed its not detecting all the percentage lines:

Code:
Nrow = 2
For Each cell In Sheets("PPIIIFORM").Range("F4:U644")
    If cell.Value <> "" Then 
    If Right(cell.NumberFormat, 4) = "0.00%" Then
       Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10. Percentage 1 Decimals f%%"
    ElseIf Right(cell.NumberFormat, 4) = "0.00" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10. Number 2 Decimals f%"
    ElseIf Right(cell.NumberFormat, 1) = "%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%%"
    Else
        Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%"
    End If
    Nrow = Nrow + 1
    Application.StatusBar = cell.Address
Next cell

Last edited by jeskit; May 4th, 2011 at 04:10 AM..
 
Old May 4th, 2011, 04:23 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

yes it can be used in a loop. Are what I named the variables self-documenting enough for you to understand what's going on?

As far as it not catching the percentage ones, it's because your Right() function is grabbing only the last 4, it needs to grab the last 5 as "0.00%" is a total of 5 characters.

But go with what I set up for it to be easier, it should be easier to maintain and understand if you need to come back to it in 6 months.
 
Old May 4th, 2011, 04:29 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

I just edited the easier code. I put in a comment for where the loop should start and where it should end :)
 
Old May 4th, 2011, 04:47 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

I tried the code you sent and it worked but it was returning decimals which were not there on some cells it returned 7 and i checked the cell it had no decimal places.

I had a go at writing it and i tested the below if statement it works but it retunrs results like "%10.0.00f%". Do you know of a way to have to just return the number of decimal points?

Also do you know of way of using a wild card in this part
Code:
 ElseIf Right(cell.NumberFormat, 4) = "0.00" Then
Code:
    If Right(cell.NumberFormat, 4) = "0.0%" Then
       Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10." & cell.NumberFormat & "f%%"
    ElseIf Right(cell.NumberFormat, 4) = "0.00" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10." & cell.NumberFormat & "f%"
    ElseIf Right(cell.NumberFormat, 1) = "%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%%"
    Else
        Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%"
    End If

Edit: I have been looking at the easy code you wrote and i think i know why its giving funny decimals its because a few of the cells are general formatted (i'm enquiring whether they should be) but is there a way to add a clause of if its not a number or its a number but 0 decimal places then it enters "%10.0f%?

Last edited by jeskit; May 4th, 2011 at 05:12 AM..
 
Old May 4th, 2011, 05:16 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

not exactly sure what you're looking for so i guessed. To return just the percent/decimal adjust the last expression:
Code:
Di
    'Now you should have all the pieces you need so just put it together:
    Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = stCurrentStyle & " " & iDecimals &  " " & "Decimals"
As far as it returning 7 decimals, just step thru the code in debug to see what it's doing (hovering over variables, right clicking them, adding a quick watch and using the watch window). I just tried it with the cell formatted with the "Comma" format and the cell.NumberFormat returned "_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)". I don't know what other formats your cells may have, so you'll have to do some testing yourself. My advice is to just test the number itself for the amount of digits after the decimal point, so something like this:
Code:
Dim stFormatStyle As String
Dim iPeriodPosition As Integer
Dim iDecimals As Integer
Dim bIsPercentage As Boolean
Dim stCurrentStyle As String

'start loop here

    stFormatStyle = cell.NumberFormat

    If Right(stFormatStyle, 1) = "%" Then
         bIsPercentage = True
         stCurrentStyle = "Percent"
    Else
         bIsPercentage = False
         stCurrentStyle = "Decimal"
    End If
    'use the number itself to find the decimal point position (i believe you called your range variable cell):
    iPeriodPosition = InStr(cell, ".")
    iDecimals = Len(cell) - iPeriodPosition
    If bIsPercentage Then iDecimals = iDecimals - 1

    'Now you should have all the pieces you need so just put it together:
    Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = stCurrentStyle & " " & iDecimals & " " & "Decimals"
'next loop
 
Old May 4th, 2011, 05:32 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

It is very confusing but what the if statement needs to do is loop through the cell range and for each cell check the formatting and then populate the column I in the Input Reference sheet using the following rules:

If its a number then it should return to column I "%10.(whatever decimal it is, should include 0"f%"

If its a percentage then it should return to column I "%10.(whatever decimal it is, should include 0"f%%"

And anything else should return "%10.0f%"

Does this help? (i am not very good at explaining things, as it makes sense in my head but not to others)

And i worked out why it was returning odd decimals. It was returning 7 because that cell was general (which i have changed and is now number) and it was returning alot of 1 decimals because alot of the cells are 0 decimal places and most the 1 decimal places it retuned where for cells which were 0 decimal places.
 
Old May 4th, 2011, 05:44 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

do you know how to step thru code? I think you could probably answer some of your own questions if you do that. Use the debug toolbar (hit View > Toolbars > Debug if you don't see it). There's a button on the debug toolbar that if you hover over it will pop up "step into". That's the one you want. If you try to start in a sub that takes parameters it won't work, so just make a dummy sub that calls that sub passing the appropriate parameter. A quick way to see what something is is to use a msgbox to pop up something, but you'll find it's easier to use the quickwatch window.

As far as the number of digits after the decimal point in the final string to be entered into l column, that is gotten and put into the iDecimals variable.
 
Old May 4th, 2011, 06:36 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

I have stepped through but not really sure what to look for.

I tried changing the iDecimals variable so it was 0 instead of 1 but its still not picking them up.
Code:
 If bIsPercentage Then iDecimals = iDecimals - 0
I was wondering if could explain what this bit of code is doing?
Code:
Right(stFormatStyle, 1)
 
Old May 4th, 2011, 08:44 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

BREAK THROUGH!

I think i have worked out why its not including 0 decimal places. The line below is looking a the decimal point but with a 0 decimal place formatted cell there is no point so when it gets to the line after the one below it defaults to 1.

Code:
 iPeriodPosition = InStr(stFormatStyle, ".")
Is there a way to put an If statement around that which says if iPeriodPosition is 0 then etc etc? I had a go at it but it didn't work and put 0 in everything

Code:
        stFormatStyle = cell.NumberFormat

        If Right(stFormatStyle, 1) = "%" Then
            bIsPercentage = True
            stCurrentStyle = "Percent"
            stending = "f%%"
        Else
            bIsPercentage = False
            stCurrentStyle = "Decimal"
            stending = "f%"
        End If

        If iPeriodPosition = 0 Then
        Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10." & iDecimals & stending
        ElseIf iPeriodPosition = InStr(stFormatStyle, ".") Then
        iDecimals = Len(stFormatStyle) - iPeriodPosition
        If bIsPercentage Then iDecimals = iDecimals - 1
        End If
        Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10." & iDecimals & stending
        Nrow = Nrow + 1
        Application.StatusBar = cell.Address
    End If





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Cell Format sumith ASP.NET 2.0 Professional 0 January 5th, 2010 05:34 AM
Checking an ICQ number SKYDOS BOOK: Beginning Regular Expressions 3 November 9th, 2008 03:55 PM
Checking cell value is integer ozPATT Excel VBA 2 October 5th, 2005 06:43 AM
Checking for upper,lower case, number and symbol dumbdumb SQL Server 2000 1 March 18th, 2004 03:51 AM
Checking for a number aware Classic ASP Basics 5 June 5th, 2003 09:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.