Check & Convert cell Value if condition auto

[SteveT]

Hello,

Sheet 1, Column A is populated with Ref #s
I would macro to check value and alter (if) according to below:

LEN(VALUE) = 6 then "111"+VALUE
LEN(VALUE) = 7 then VALUE
VALUE already begins with "111" then VALUE

I believe an event macro in sheet is route.... that is as far as i can take
it.
Any help appreciated, I've searched for awhile but solution found not.

Thanks in advance.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub sonic()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A1:A" & lastrow)
For Each c In myrange
If Len(c) = 6 And Left(c.Value, 3) <> 111 Then
c.Value = 111 & c.Value
End If
Next
End Sub

Mike

 

[SteveT]

Thanks Mike... Is there anyway to automatically run upon user exiting the
cell ?

BR, Steven

 

[Mike H]

Hi,

You could try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:a")) Is Nothing Then
If Len(Target) = 6 And Left(Target.Value, 3) <> 111 Then
Target.Value = 111 & Target.Value
End If
End If


Mike