checking the number format of a cell

  • Thread starter Thread starter Legacy 93538
  • Start date Start date
L

Legacy 93538

Guest
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 place.

Code:
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 its doing 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

Jessicaseymour
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi

I have been altering my code and i have change my if statement it works on the number formatting but not on percentage formatting.

I tested the code below and it picks up all the number formmating with decimal points but its not picking up the percentage decimal points and its only picking up some of the percentage formatted cells.

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 2 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

If anyone can help me it would be great!!
 
Upvote 0

Forum statistics

Threads
1,224,599
Messages
6,179,827
Members
452,946
Latest member
JoseDavid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top