Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit String VB Macro
I need to create a macro that will edit a simple string for me so I
don't have to do it by hand. Qualifications 1) Need to be able to set the range (IE B1:H8) or whatever I need 2) The edit must read different lengths text (see examples) 3) Read and then place on letter at the end of the string. 4) Delete character and extra spaces. Here is what the current format looks like. T | TI06 Here is what I need to to look like TI06T Here is another example N | KOS21 to KOS21N In laymens terms it would be. 1) Read first character place at the end of the string. 2) Delete the first four characters/spaces of the string. (This is a constant.) Any help out there for me? Pretty please =) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit String VB Macro
Hi Lunch,
Try: '============= Public Sub Tester001() Dim rng As Range Dim rCell As Range Set rng = Selection '<<==== CHANGE For Each rCell In rng.Cells With rCell On Error Resume Next If Not IsEmpty(.Value) Then .Value = Mid(.Value, 5) & Left(.Value, 1) End If On Error GoTo 0 End With Next rCell End Sub '<<============= --- Regards, Norman "Lunch" wrote in message oups.com... I need to create a macro that will edit a simple string for me so I don't have to do it by hand. Qualifications 1) Need to be able to set the range (IE B1:H8) or whatever I need 2) The edit must read different lengths text (see examples) 3) Read and then place on letter at the end of the string. 4) Delete character and extra spaces. Here is what the current format looks like. T | TI06 Here is what I need to to look like TI06T Here is another example N | KOS21 to KOS21N In laymens terms it would be. 1) Read first character place at the end of the string. 2) Delete the first four characters/spaces of the string. (This is a constant.) Any help out there for me? Pretty please =) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit String VB Macro
Norman,
Thanks for the quick reply! I'm still learning VB and such so I'm trying to read the code, figure out what it's doing as well as learn how this integrates with excel and it's quarks. Naturally I got snagged on the simple part after you did everything else..................... "Change" DOH! '============= Public Sub Tester001() Dim rng As Range Dim rCell As Range Set rng = ("I1:AK1") '<<==== CHANGE For Each rCell In rng.Cells With rCell On Error Resume Next If Not IsEmpty(.Value) Then .Value = Mid(.Value, 5) & Left(.Value, 1) End If On Error GoTo 0 End With Next rCell End Sub '<<============= Results in: Compile error: Type Mismatch - Same error occurs if I run "I1:AK1" - Just I1:AK1 results in "Compile error: Sub or Function not defined" Just for kicks here is a partial list of some of the variables. T | TI01 D | TI01 C | TI01 T | TI02 D | TI02 C | TI02 T | TI03 D | TI03 C | TI03 T | TI04 If I'm reading the code right, and I'd like to think I am, after pulling out the old VB book I think I see what it's doing. =) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit String VB Macro
Hi Lunch,
Try changing: Set rng = ("I1:AK1") '<<==== CHANGE to Set rng = Range("I1:AK1") '<<==== CHANGE --- Regards, Norman "Lunch" wrote in message oups.com... Norman, Thanks for the quick reply! I'm still learning VB and such so I'm trying to read the code, figure out what it's doing as well as learn how this integrates with excel and it's quarks. Naturally I got snagged on the simple part after you did everything else..................... "Change" DOH! '============= Public Sub Tester001() Dim rng As Range Dim rCell As Range Set rng = ("I1:AK1") '<<==== CHANGE For Each rCell In rng.Cells With rCell On Error Resume Next If Not IsEmpty(.Value) Then .Value = Mid(.Value, 5) & Left(.Value, 1) End If On Error GoTo 0 End With Next rCell End Sub '<<============= Results in: Compile error: Type Mismatch - Same error occurs if I run "I1:AK1" - Just I1:AK1 results in "Compile error: Sub or Function not defined" Just for kicks here is a partial list of some of the variables. T | TI01 D | TI01 C | TI01 T | TI02 D | TI02 C | TI02 T | TI03 D | TI03 C | TI03 T | TI04 If I'm reading the code right, and I'd like to think I am, after pulling out the old VB book I think I see what it's doing. =) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit String VB Macro
Norman,
You just made my day. =) If I could hug you I would. Thanks a ton! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit REplace String | Excel Worksheet Functions | |||
Need to edit string in cell | Excel Discussion (Misc queries) | |||
Reset row hight after string of text edit on a cell | Excel Discussion (Misc queries) | |||
edit text string in column B | Excel Discussion (Misc queries) | |||
Macro to edit text string data input | Excel Programming |