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
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
location range | 4 | 22 | |
Looking for macro to do version saving of workbooks in Excel 2016. | 6 | 35 | |
COPY from excel to notepad | 3 | 32 | |
Most Consistent Performer | 4 | 19 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
22 Experts available now in Live!