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 (http://msdn.microsoft.com/en-us/library/aa223886(v=office.11).aspx)