CODEWITHSUNDEEP
excelvba
Lawrence Forster
Lawrence Forster

Reputation: 53

Is there a way to reference if cells have decimal point in vba?

I want to have create some code in VBA that will look through the cells in a worksheet in column B. If the code finds a cell value with a decimal (opposed to a whole number e.g. not 1, but 1.1) then print the cell value to right off it in another sheet.

I know it will be a loop and it will use offset hit etc. I havent tried this code in VBA i have just typed it as an example of what i will do in the question. 

For each cell in Sheets("Sub Tasks").Range("B1:B" & LastRow)
     If cell = '(DECIMAL FORUMLA) Then

Upvotes: 1

Views: 4463

Answers (2)

41686d6564
41686d6564

Reputation: 19661

You may use something like this:

Dim cell As Range
For Each cell In Sheets("Sub Tasks").Range("B1:B" & LastRow)
    If IsNumeric(cell.Value) Then
        If Val(cell.Value) <> Int(cell.Value) Then
            ' The number contains decimals.
            Dim valueToTheRight As Variant
            valueToTheRight = cell.Offset(0, 1).Value
            ' TODO: Add `valueToTheRight` into the appropriate place of the other sheet.
        End If
    Else
        ' The cell value is not a number.
        ' TODO: either do something about it or remove the `else` branch to ignore it.
    End If
Next

Upvotes: 3

Tom
Tom

Reputation: 9898

You could evaluate the cells content?

Sub Demo()
    Dim cell

    For each cell in Sheets("Sub Tasks").Range("B1:B" & LastRow)
        If IsNumeric(c.Value2) And InStr(c.Value2, Application.DecimalSeparator) Then
            Debug.Print c.Value2
        End If
    Next c
End Sub

IsNumeric will test if the cell holds a number. i.e. skipping any strings with full stops in. and Instr(c.value2, Application.DecimalSeparator) will test for the decimal

Upvotes: 1

Related Questions