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

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

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

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

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