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.

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" wrote:

> 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.
>
>

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

BR, Steven



"Mike H" wrote:

> 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" wrote:
>
> > 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.
> >
> >

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

"SteveT" wrote:

> Thanks Mike... Is there anyway to automatically run upon user exiting the
> cell ?
>
> BR, Steven
>
>
>
> "Mike H" wrote:
>
> > 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" wrote:
> >
> > > 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.
> > >
> > >