Count number of periods in target.value and indent

[FryGirl]

In VBA I would like to have a Worksheet_Change event which when somebody types in a value in column A the macro would do two things.

First, check to make sure the value ends in a period and then indent the value based on the number of periods found?

This is what I have so far...it checks for the ending period and indents, but not sure how to return the number of periods which I would like to use to replace the hard coded IndentLevel. Is this the right way to go or is there a better way?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As String
    If Target.Column <> 1 Then Exit Sub
    i = Right(Target.Value, 1)
    If i = "." Then
        MsgBox Len(Split(Target.Value)(0))
        Range(Target.Address).IndentLevel = 2
    Else
        MsgBox "Please enter a number which ends in a period", vbInformation
    End If
End Sub

 

[Dryver14]

whilst i think on this how are you getting the full stop (Period) to stay at the end of a number when it is keyed in?

Ive run your macro with text and it is fine but the period disappears when I key a number in the column

 

[FryGirl]

Sorry about that, I type in A1.1.

 

[AlphaFrog]

This will count the number of periods

PeriodCount = Len(Target.Value) - Len(Replace(Target.Value, ".", ""))

 

[Dryver14]

I'm not so brilliant at this but have you tried dropping the variable i

and just saying if right (target.value,1) = "." then

followed by

if right (target.value,2) = ".."

 

[Blade Hunter]

Maybe this?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Target.Column <> 1 Then Exit Sub
    i = Len(Target.Text) - Len(Replace(Target.Text, ".", ""))
    If i > 0 Then
        Range(Target.Address).IndentLevel = i
    Else
        MsgBox "Please enter a number which ends in a period", vbInformation
    End If
End Sub

Edit: Hmmmm, second thoughts that counts all periods, not just at the end

 

[FryGirl]

Thanks AlphaFrog that does it. Thanks to all others also