Checking number format within a cell

jeskit · May 3rd, 2011, 09:49 AM

Hi
I have an IF statement inside a loop which loops through a cell range on a sheet called "PPIIIFORM" and my if statement should be checking the number format of the cell and doing the following

If its a percentage but no decimal point in sheet "Input_Reference_Table" on the 9th column place "%10.0f%%"

ElseIf its a percentage and has a decimal point in sheet "Input_Reference_Table" on the 9th column place "%10.(decimal point number)f%%"

ElseIf its a number with a decimal point sheet "Input_Reference_Table" on the 9th column place "%10.(decimal point number)f%"

Else place "%10.0f%" in the "Input_Reference_Table" on the 9th column.

Code:

Sub PP3InputRef()
Dim Nrow As Long
Dim cell As Range

For Each cell In Sheets("PPIIIFORM").Range("F4:U644")
 If cell.Value <> "" Then Sheets("Input_Reference_Table").Cells(Nrow, 1).Value = cell.Value

If Right(cell.NumberFormat, 1) = "%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%%"
ElseIf Right(cell.NumberFormat, 1) = "0.000%" Then
     Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = ParseNFmt(cell.NumberFormat)
ElseIf Right(cell.NumberFormat, 1) = "0.000" Then
     Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = ParseNFmt2(cell.NumberFormat)
Else
     Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%"
End If
    Nrow = Nrow + 1
    Application.StatusBar = cell.Address
Next cell

End Sub

Function ParseNFmt(NFmt As String) As String
Dim NDec As Long, NType As String
NType = IIf(Right(NFmt, 1) = "%", "Percent", "Number")
If NFmt Like "*.*" Then ' has decimals
    NDec = Len(NFmt) - InStr(NFmt, ".")
    If NType = "Percent" Then NDec = NDec - 1
Else
    NDec = 0
End If
ParseNFmt = NType & " " & NDec & " decimals"
End Function
 
Function ParseNFmt2(NFmt As String) As String
Dim NDec As Long, NType As String
NType = IIf(Right(NFmt, 1) = "0", "Number")
If NFmt Like "*.*" Then ' has decimals
    NDec = Len(NFmt) - InStr(NFmt, ".")
    If NType = "Number" Then NDec = NDec - 1
Else
    NDec = 0
End If
ParseNFmt = NType & " " & NDec & " decimals"
End Function

However itsdoing all of it except placing the decimal point number in the cells which have decimal points and i know some of them do have a decimal points.

Can anyone help me?

Thanks

Jeskit

mtranchi · May 3rd, 2011, 10:03 AM

One thing you can try to do is passing your ParseNFmt function a hardcoded string that you know has a decimal point, then stepping thru the code to see where the problem is. It seems the problem would be in your If test:

Code:

If NFmt Like "*.*" Then ' has decimals

I've never used the "Like" keyword so not familiar with it. Try using the "Instr" function as your test.

Code:

'The Instr function will return 0 if the text being searched for doesn't exist
If Instr(NFmt, ".") > 0 Then ' has decimals

You can make your code more readable and also execute faster by using a variable for the position of the decimal point and using that

Code:

Function ParseNFmt(NFmt As String) As String
Function ParseNFmt(NFmt As String) As String
Dim NDec As Long, NType As String

Dim iDecimal as Integer
iDecimal = Instr(NFmt, ".")

NType = IIf(Right(NFmt, 1) = "%", "Percent", "Number")
If iDecimal > 0 Then ' has decimals
    NDec = Len(NFmt) - iDecimal
    If NType = "Percent" Then NDec = NDec - 1
Else
    NDec = 0
End If
ParseNFmt = NType & " " & NDec & " decimals"
End Function

jeskit · May 3rd, 2011, 10:51 AM

hI

I tried that and i am still not sure what its doing. I know it works when its looking at whether its a percentage of a number.

But i need to also look at how many decimal points there are so i was thing could the two lines below be adapted to include decimal points so it at both a percentage and decimal points and number and decimal points.

Code:

If 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%"

mtranchi · May 3rd, 2011, 11:06 AM

I know nothing of working with number formats of cells but maybe you could do something like this:

Code:

If 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
'Looks like no matter what happens in your If blocks you do this, so have it after the If blocks like so:
Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = ParseNFmt2(cell.NumberFormat)

mtranchi · May 3rd, 2011, 11:12 AM

To step thru your code to see what it's doing you put your cursor in the sub where you want to start and press F8 on your keyboard. Then keep pressing F8 to step thru the next line. You can hover over varaibles to see their value or add watches by right clicking on the variable and selecting "Add watch," then opening the "Watch" window.

jeskit · May 3rd, 2011, 11:25 AM

Hi

I have made a breakthrough!

I tried it like you said so the decimal point part of the IF statement was after the if statement and was in a seperate column.

It worked perfectly so i tried to combine it so i had:

Code:

    If Right(cell.NumberFormat, 1) = "%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%%"

    ElseIf Right(cell.NumberFormat, 1) = "0.00%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10." & ParseNFmt(cell.NumberFormat) & "f%%"

    Else
     Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%"
    End If

But it didn't work it just ignored the decimal point line. When it was in seperate column it puts "Number 2 decimals" in the cells. so i'm not sure if its the function or the if statement

mtranchi · May 3rd, 2011, 11:37 AM

it looks like you can just skip your ParseNFmt function altogether as you're already testing how many decimal points you have. Try this:

Code:

If Right(cell.NumberFormat, 1) = "%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%%"

'Try just entering the string you want directly
    ElseIf Right(cell.NumberFormat, 1) = "0.00%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10. Percentage 2 Decimals f%%"



    Else
     Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%"
    End If

mtranchi · May 3rd, 2011, 11:42 AM

i just noticed the problem. When you're testing the number format you're only returning the last character for the percentage thing:

Code:

ElseIf Right(cell.NumberFormat, 1) = "0.00%" Then

'you need to check the last 5 characters like so:
ElseIf Right(cell.NumberFormat, 5) = "0.00%" Then

you need to change the number of characters to check with the Right() function from 1 to 5

jeskit · May 3rd, 2011, 11:52 AM

Hi

Great that works!

One last question how would you rewrite that line of code so it doesn't look at percentages just numbers?

Code:

    ElseIf Right(cell.NumberFormat, 1) = "0.00%" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10. Percentage 2 Decimals f%%"

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

i think you can just specify the number format in one of your ElseIf blocks:

Code:

    ElseIf Right(cell.NumberFormat, 4) = "0.00" Then
      Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10. Number 2 Decimals f%%"

There's probably a more succinct way to do it too. Try 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

    iPeriodPosition = Instr(stFormatStyle, ".")
    iDecimals = len(stFormatStyle) - 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 = "%10. " & stCurrentStyle & " " & iDecimals &  " " & "Decimals f%%"

'next loop

Didn't test it so you may have to debug. But this way, no matter how many decimals you may have and whether or not it's a percentage formatted style, this will pick it up and you don't have to write an ElseIf block for each one.