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