VBA Function To Extract Decimal Numbers

[PcMax]

Hello everyone

I'm confused ...
I would like to achieve a cycle optimized to achieve the two-digit decimal
Values ​​on the sheet were up to 2 decimal places
Type: D100 = 12345.67 becomes E100= 67
Type: D101 = 12345.6 becomes E100= 60
Etc..
Here's how to get the result

    For Each MyVar In Range("D1:D" & Cells(Rows.Count, 2).End(xlUp).Row)
        If IsNumeric(MyVar) Then
            Cells(MyVar.Row, 9) = Int((MyVar - Int(MyVar)) * 100)
        End If
    Next

I hope there is a different solution

[littleiitin]

Hi PcMax,

Definitely there are other methods to achieve your requirement.
However I believe the method you applied is the best one.
Why you need other solution?

Thanks
Rahul Singh

[Admin]

Hi,

Here is a method which doesn't require loop

Dim x   As String

With Range("d1:d" & Range("b" & Rows.Count).End(xlUp).Row)
    x = .Address
    .Offset(, 5) = Evaluate("if(isnumber(" & x & "),(" & x & "-int(" & x & "))*100," & x & ")")
End With

[PcMax]

Hallo

And if I changed the formula to avoid decimal numbers with more 12.99909938 type or how I could use the assessment process that is not possible

Int(MyVar * 100) Mod 100

[Rasm]

Try this - a lot of steps - but should work - should not matter how many decimal places

    Asingle = 1.2345
    Astr = Str(Asingle)
    Aint = InStr(1, Astr, ".")
    If Aint > 0 then Along = Val(Right(Astr, Len(Astr) - Aint)) else Along = 0

ohhh - just realized you want 2 characters - so use the Mid command in place of the Right command

[Admin]

Hi,

replace

.Offset(, 5) = Evaluate("if(isnumber(" & x & "),(" & x & "-int(" & x & "))*100," & x & ")")

with

.Offset(, 5) = Evaluate("if(isnumber(" & x & "),round((" & x & "-int(" & x & ")),2)*100," & x & ")")

HTH

[PcMax]

Hallo

Wonderful!

Sub Admin()
    Dim x   As String
    With Range("D2:E" & Range("B" & Rows.Count).End(xlUp).Row)
        x = .Address
        .Offset(, 5) = Evaluate("if(isnumber(" & x & "),round((" & x & "-int(" & x & ")),2)*100," & x & ")")
    End With
End Sub

This code was looking for, you can teach Evaluate method

[Admin]

Hi,

Thanks for the feedback.
Read Evaluate Method