Check & Convert cell Value if condition auto
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. |
Check & Convert cell Value if condition auto
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. |
Check & Convert cell Value if condition auto
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. |
Check & Convert cell Value if condition auto
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. |
All times are GMT +1. The time now is 05:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com