Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto filter with condition | Excel Discussion (Misc queries) | |||
Display in same cell lbs(kgs) using formula to auto-convert lbs | New Users to Excel | |||
auto change cell text colour resulting from a condition | Excel Worksheet Functions | |||
Continually check if condition is true in VBA | Excel Discussion (Misc queries) |