Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if there is a decimal point
Hello,
Within a for loop i need to check if and where a value has a decimal point, and then amend/add a decimal point. Ie: 100.11 - 100.11 100.1 - 10.01 100 - 1.00 I have tried convoluted ways o check such as Right(Left(c.value,3),1)="." but it doesn't ever want to... Any ideas, suggestions? Much Appreciated, Janos |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if there is a decimal point
Try c.Text instead of c.Value, and use InStr(c.Text, ".")0 as an initial
screen for the point. To see if a six-character string has a decimal point in its 4th position: Mid$(c.Text, Len(c.Text)-2, 1)="." - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Janos" wrote in message ... Hello, Within a for loop i need to check if and where a value has a decimal point, and then amend/add a decimal point. Ie: 100.11 - 100.11 100.1 - 10.01 100 - 1.00 I have tried convoluted ways o check such as Right(Left(c.value,3),1)="." but it doesn't ever want to... Any ideas, suggestions? Much Appreciated, Janos |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if there is a decimal point
Hi Janos,
Your rules for the movement of the decimal point are not clear to me, but try something like: '============= Public Sub TesterB002() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim rCell As Range Dim sStr As String Dim iLastRow As Long Dim iPos As Long Set WB = Workbooks("MyBook.xls") '<<=== CHANGE Set SH = WB.Sheets("Sheet1") '<<=== CHANGE Set Rng = SH.Range("A2:A100") '<<=== CHANGE For Each rCell In Rng.Cells With rCell If IsNumeric(.Value) _ And Not IsEmpty(.Value) Then sStr = Replace(.Value, ".", vbNullString, 1) iPos = InStr(1, .Value, ".") Select Case iPos Case 0: .Value = Left(sStr, 1) _ & "." & Mid(sStr, 2) Case 3: .Value = .Value = Left(sStr, 2) _ & "." & Mid(sStr, 3) End Select .Value = CDbl(.Value) End If End With Next rCell Rng.NumberFormat = "0.00" End Sub '<<============= --- Regards, Norman "Janos" wrote in message ... Hello, Within a for loop i need to check if and where a value has a decimal point, and then amend/add a decimal point. Ie: 100.11 - 100.11 100.1 - 10.01 100 - 1.00 I have tried convoluted ways o check such as Right(Left(c.value,3),1)="." but it doesn't ever want to... Any ideas, suggestions? Much Appreciated, Janos |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if there is a decimal point
Your examples are not very clear, but based on what you have provided, I think this will work: Sub test() Dim strText As String strText = "1234" strText = Replace(strText, ".", "") strText = Left(strText, Len(strText) - 2) & "." & Right(strText, 2) End Sub -- Hope that helps. Vergel Adriano "Janos" wrote: Hello, Within a for loop i need to check if and where a value has a decimal point, and then amend/add a decimal point. Ie: 100.11 - 100.11 100.1 - 10.01 100 - 1.00 I have tried convoluted ways o check such as Right(Left(c.value,3),1)="." but it doesn't ever want to... Any ideas, suggestions? Much Appreciated, Janos |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if there is a decimal point
Thank you all for your help, I solved it by treating it like a string.
Janos "Janos" wrote: Hello, Within a for loop i need to check if and where a value has a decimal point, and then amend/add a decimal point. Ie: 100.11 - 100.11 100.1 - 10.01 100 - 1.00 I have tried convoluted ways o check such as Right(Left(c.value,3),1)="." but it doesn't ever want to... Any ideas, suggestions? Much Appreciated, Janos |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimal point | Excel Discussion (Misc queries) | |||
Decimal point | New Users to Excel | |||
Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
decimal point | Excel Worksheet Functions | |||
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) |