EDITED: TRUE & FALSE wrong way round
Function IsFormattedAsWholeNumber(rng As Range) As Boolean
Dim fStr As String
fStr = rng.NumberFormat
If InStr(fStr, ".") = 0 Then
IsFormattedAsWholeNumber = True
End If
End Function
Now use the above function like this...=IsFormattedAsWholeNumber(A1)
The above function will return TRUE if the number 123.45 is custom formatted to show as 123, a whole number.
Sub ReCalculate()
Calculate
End Sub
Function IsFormattedAsWholeNumber(rng As Range) As Boolean
Dim fStr As String
If rng.NumberFormat = "General" Then
If rng - Int(rng) = 0 Then IsFormattedAsWholeNumber = True
Else
fStr = rng.NumberFormat
If InStr(fStr, ".") = 0 Then
IsFormattedAsWholeNumber = True
End If
End If
End Function
Function IsFormattedAsWholeNumber(rng As Range) As Boolean
Dim fStr As String
If rng.Value <> "" Then
If rng.NumberFormat = "General" Then
If rng - Int(rng) = 0 Then IsFormattedAsWholeNumber = True
Else
fStr = rng.NumberFormat
If InStr(fStr, ".") = 0 Then
IsFormattedAsWholeNumber = True
End If
End If
End If
End Function
And again, after changing the number format of any cell if you click on Calculate Sheet, it will recalculate all the functions on the sheet.
'The following function has only one argument i.e. a cell reference
Function IsFormattedAsWholeNumber(rng As Range) As Boolean
'Declaring the String variable to hold the number format of the cell
Dim fStr As String
'Checking if the cell is not blank
If rng.Value <> "" Then
'Checking if the number format of the cell is not the default "General" format
If rng.NumberFormat = "General" Then
'Checking if the cell contains a whole number and if so, the function will return True
If rng - Int(rng) = 0 Then IsFormattedAsWholeNumber = True
'If above condition is not met, the control passes to the Else statement
Else
'fStr String variable holds the custom number format of the cell
'So if the custom number format is whole number, fStr would be equal to 0
'And if the custom number format is a decimal number, fStr would be equal to 0.0 or 0.00 depending upon the decimal places
fStr = rng.NumberFormat
'Checking if the fStr which holds the custom number format as a string doesn't contain a . (period) in it
'That means the custom number format is 0 i.e. a whole number, hence the function would return True in this case
'And if this condition is not met, that means the custom number format string contains a . (period) in it indicating that the cell has a decimal number format
If InStr(fStr, ".") = 0 Then
IsFormattedAsWholeNumber = True
End If
End If
End If
End Function
Title | # Comments | Views | Activity |
---|---|---|---|
Compile Error: Expected List Separator or) error message | 5 | 29 | |
Extracting P&ID Reference and the drawing number from a PDF Isometric drawing into excel | 6 | 29 | |
SUM every other column | 10 | 36 | |
Excel VBA - copying formats | 7 | 17 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
11 Experts available now in Live!