Hello. Please help me if you can.

I'm trying to set up an on sheet change, which I can get
to fire, but not successfully.

My code is supposed to reformat a range of cells from this:

123123123412345612312123

to this:

123.123.1234.123456.123.12.123

It must also ensure that the pattern is entered correctly,
i.e. not text or a partial set of values, etc.

Here is my code in a sheet module (you should be able to
copy directly to a module):

Private Sub Worksheet_Calculate()

Dim rngCell As Range

For Each rngCell In Range("A24:A30").Rows
If Not rngCell.FormulaR1C1 = "" And Not _
rngCell.FormulaR1C1
Like "###.###.####.######.###.##.###" Then
rngCell.Activate
MsgBox "Invalid account distribution!",
vbCritical, _
"INVALID ACCOUNT DISTRIBUTION!": Exit Sub
End If
Next rngCell

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)

Dim rngCell As Range
Dim strAccount As String

For Each rngCell In Range("A24:A30").Rows
If rngCell.FormulaR1C1 Like "########################"
Then
strAccount = rngCell.FormulaR1C1
strAccount = Left(strAccount, 3) & "." & _
Mid(strAccount, 4, 3) & "." & _
Mid(strAccount, 7, 4) & "." & _
Mid(strAccount, 11, 6) & "." & _
Mid(strAccount, 17, 3) & "." & _
Mid(strAccount, 20, 2) & "." & _
Right(strAccount, 3)
rngCell.FormulaR1C1 = strAccount
End If
Next rngCell

End Sub

Your example code would be most helpful. Thanks in advance.

Raj

I'm not sure you can do what you want. I don't think you can have a number
with 24 digits.

If for example, you enter 123456789012345678901234, a 24 digit number, Excel
will display it as 1.23456789012345E+23 in the formula bar and 1.234567E+23
in the cell. That's with format general. If you change it to numeric
format it then displays as 1.23456789012345E+23 in the formula bar and
1.23456789012345000000000.00 in the cell.

So I don't think you can have the format you want is the shorter answer.
Probably something to do with 15 digits precision.

Regards

Trevor


"raj" wrote in message
...
Hello. Please help me if you can.

I'm trying to set up an on sheet change, which I can get
to fire, but not successfully.

My code is supposed to reformat a range of cells from this:

123123123412345612312123

to this:

123.123.1234.123456.123.12.123

It must also ensure that the pattern is entered correctly,
i.e. not text or a partial set of values, etc.

Here is my code in a sheet module (you should be able to
copy directly to a module):

Private Sub Worksheet_Calculate()

Dim rngCell As Range

For Each rngCell In Range("A24:A30").Rows
If Not rngCell.FormulaR1C1 = "" And Not _
rngCell.FormulaR1C1
Like "###.###.####.######.###.##.###" Then
rngCell.Activate
MsgBox "Invalid account distribution!",
vbCritical, _
"INVALID ACCOUNT DISTRIBUTION!": Exit Sub
End If
Next rngCell

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)

Dim rngCell As Range
Dim strAccount As String

For Each rngCell In Range("A24:A30").Rows
If rngCell.FormulaR1C1 Like "########################"
Then
strAccount = rngCell.FormulaR1C1
strAccount = Left(strAccount, 3) & "." & _
Mid(strAccount, 4, 3) & "." & _
Mid(strAccount, 7, 4) & "." & _
Mid(strAccount, 11, 6) & "." & _
Mid(strAccount, 17, 3) & "." & _
Mid(strAccount, 20, 2) & "." & _
Right(strAccount, 3)
rngCell.FormulaR1C1 = strAccount
End If
Next rngCell

End Sub

Your example code would be most helpful. Thanks in advance.

You got a couple responses yesterday:
http://groups.google.com/groups?thre...5FF6%40msn.com

(Are you punchy?)

raj wrote:

Hello. Please help me if you can.

I'm trying to set up an on sheet change, which I can get
to fire, but not successfully.

My code is supposed to reformat a range of cells from this:

123123123412345612312123

to this:

123.123.1234.123456.123.12.123

It must also ensure that the pattern is entered correctly,
i.e. not text or a partial set of values, etc.

Here is my code in a sheet module (you should be able to
copy directly to a module):

Private Sub Worksheet_Calculate()

Dim rngCell As Range

For Each rngCell In Range("A24:A30").Rows
If Not rngCell.FormulaR1C1 = "" And Not _
rngCell.FormulaR1C1
Like "###.###.####.######.###.##.###" Then
rngCell.Activate
MsgBox "Invalid account distribution!",
vbCritical, _
"INVALID ACCOUNT DISTRIBUTION!": Exit Sub
End If
Next rngCell

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)

Dim rngCell As Range
Dim strAccount As String

For Each rngCell In Range("A24:A30").Rows
If rngCell.FormulaR1C1 Like "########################"
Then
strAccount = rngCell.FormulaR1C1
strAccount = Left(strAccount, 3) & "." & _
Mid(strAccount, 4, 3) & "." & _
Mid(strAccount, 7, 4) & "." & _
Mid(strAccount, 11, 6) & "." & _
Mid(strAccount, 17, 3) & "." & _
Mid(strAccount, 20, 2) & "." & _
Right(strAccount, 3)
rngCell.FormulaR1C1 = strAccount
End If
Next rngCell

End Sub

Your example code would be most helpful. Thanks in advance.

--

Dave Peterson

I think he's right dave...


---
~~ Message posted from http://www.ExcelForum.com/