Hi All,

I have VBA code which adds 99 before 10 digits and differs if the
digits chnage its perfectly workin but if i copy paste the data say from
C1:C10
into A1 its showing an run time error13.Here's my code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Len(Target.Value) < 12 And Target.Value = 0 Then
Target.Value = Left("990000000000", 12 - Len(Target.Value)) &
Target.Value
End If
End If
End Sub

Any help would be appreciated.

Thanks & Regards,

Raja


--
Raja
------------------------------------------------------------------------
Raja's Profile: http://www.thecodecage.com/forumz/member.php?userid=497
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115104

Try this code instead...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
On Error GoTo FixItUp
Application.EnableEvents = False
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Target.Value = Format(Target.Value, "990000000000")
End If
FixItUp:
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)


"Raja" wrote in message
...

Hi All,

I have VBA code which adds 99 before 10 digits and differs if the
digits chnage its perfectly workin but if i copy paste the data say from
C1:C10
into A1 its showing an run time error13.Here's my code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Len(Target.Value) < 12 And Target.Value = 0 Then
Target.Value = Left("990000000000", 12 - Len(Target.Value)) &
Target.Value
End If
End If
End Sub

Any help would be appreciated.

Thanks & Regards,

Raja


--
Raja
------------------------------------------------------------------------
Raja's Profile: http://www.thecodecage.com/forumz/member.php?userid=497
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=115104

You might have to format the cells in the column using 000000000000 in order
to get them to display all 12 digits.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Try this code instead...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
On Error GoTo FixItUp
Application.EnableEvents = False
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Target.Value = Format(Target.Value, "990000000000")
End If
FixItUp:
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)


"Raja" wrote in message
...

Hi All,

I have VBA code which adds 99 before 10 digits and differs if the
digits chnage its perfectly workin but if i copy paste the data say from
C1:C10
into A1 its showing an run time error13.Here's my code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Len(Target.Value) < 12 And Target.Value = 0 Then
Target.Value = Left("990000000000", 12 - Len(Target.Value)) &
Target.Value
End If
End If
End Sub

Any help would be appreciated.

Thanks & Regards,

Raja


--
Raja
------------------------------------------------------------------------
Raja's Profile: http://www.thecodecage.com/forumz/member.php?userid=497
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=115104

Hi Raja,
I hate to have to remind another rule, but it's very important titles
describe your problem. When another member uses the search function, "
solution needed" won't help him much would it?

Thread Title


--
Pecoflyer

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html)
really IS fast !
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115104