Checking number format within a cell

mtranchi · May 3rd, 2011, 12:12 PM

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

jeskit · May 4th, 2011, 02:50 AM

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

mtranchi · May 4th, 2011, 04:23 AM

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.

mtranchi · May 4th, 2011, 04:29 AM

I just edited the easier code. I put in a comment for where the loop should start and where it should end :)

jeskit · May 4th, 2011, 04:47 AM

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%?

mtranchi · May 4th, 2011, 05:16 AM

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

jeskit · May 4th, 2011, 05:32 AM

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.

mtranchi · May 4th, 2011, 05:44 AM

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.

jeskit · May 4th, 2011, 06:36 AM

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)

jeskit · May 4th, 2011, 08:44 AM

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