Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to edit part of a cell
Am trying to edit data in the worksheet where I have to edit only part
of the text in the cell - using a macro. 146-P1-0502 L 145-P13-0502 L 145-P8-0403 L 145-P9-0504 L 145-P10-0406 L I only want to change the 0502 part to 0706 in all the cells. Tried using a macro to select only part of it but it changes the other data parts also. I tried the code below but then it doesn't work for 145-P13-0502 L where we have 2 digits after the P. Dim StartChar As Integer Dim EndChar As Integer StartChar = 8 EndChar = 3 ActiveCell.Characters(StartChar, EndChar).Insert (206) Please help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to edit part of a cell
try:
Dim pos1 As Integer pos1 = InStr(ActiveCell.Value, "-") pos1 = InStr(pos1 + 1, ActiveCell.Value, "-") ActiveCell.Characters(pos1 + 1, 4).Insert "0706" Good luck Fred wrote in message oups.com... Am trying to edit data in the worksheet where I have to edit only part of the text in the cell - using a macro. 146-P1-0502 L 145-P13-0502 L 145-P8-0403 L 145-P9-0504 L 145-P10-0406 L I only want to change the 0502 part to 0706 in all the cells. Tried using a macro to select only part of it but it changes the other data parts also. I tried the code below but then it doesn't work for 145-P13-0502 L where we have 2 digits after the P. Dim StartChar As Integer Dim EndChar As Integer StartChar = 8 EndChar = 3 ActiveCell.Characters(StartChar, EndChar).Insert (206) Please help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to edit part of a cell
see if this will work for you
Option Explicit Sub test() Dim cell As Range Dim a As Long Dim str As String For Each cell In Range("a1:a5") str = cell.Value cell.Value = Application.WorksheetFunction.Replace(str, _ WorksheetFunction.Find("-", str, 5) + 1, 4, "0706") Next End Sub -- Gary wrote in message oups.com... Am trying to edit data in the worksheet where I have to edit only part of the text in the cell - using a macro. 146-P1-0502 L 145-P13-0502 L 145-P8-0403 L 145-P9-0504 L 145-P10-0406 L I only want to change the 0502 part to 0706 in all the cells. Tried using a macro to select only part of it but it changes the other data parts also. I tried the code below but then it doesn't work for 145-P13-0502 L where we have 2 digits after the P. Dim StartChar As Integer Dim EndChar As Integer StartChar = 8 EndChar = 3 ActiveCell.Characters(StartChar, EndChar).Insert (206) Please help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to edit part of a cell
Thanks Fred, it works!! :-)
Is it possible to select a few and edit at once or do I have to change them one by one. Thanks heaps, CC Fred wrote: try: Dim pos1 As Integer pos1 = InStr(ActiveCell.Value, "-") pos1 = InStr(pos1 + 1, ActiveCell.Value, "-") ActiveCell.Characters(pos1 + 1, 4).Insert "0706" Good luck Fred wrote in message oups.com... Am trying to edit data in the worksheet where I have to edit only part of the text in the cell - using a macro. 146-P1-0502 L 145-P13-0502 L 145-P8-0403 L 145-P9-0504 L 145-P10-0406 L I only want to change the 0502 part to 0706 in all the cells. Tried using a macro to select only part of it but it changes the other data parts also. I tried the code below but then it doesn't work for 145-P13-0502 L where we have 2 digits after the P. Dim StartChar As Integer Dim EndChar As Integer StartChar = 8 EndChar = 3 ActiveCell.Characters(StartChar, EndChar).Insert (206) Please help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to edit part of a cell
change the range in my example and see if it does what you want.
-- Gary wrote in message oups.com... Thanks Fred, it works!! :-) Is it possible to select a few and edit at once or do I have to change them one by one. Thanks heaps, CC Fred wrote: try: Dim pos1 As Integer pos1 = InStr(ActiveCell.Value, "-") pos1 = InStr(pos1 + 1, ActiveCell.Value, "-") ActiveCell.Characters(pos1 + 1, 4).Insert "0706" Good luck Fred wrote in message oups.com... Am trying to edit data in the worksheet where I have to edit only part of the text in the cell - using a macro. 146-P1-0502 L 145-P13-0502 L 145-P8-0403 L 145-P9-0504 L 145-P10-0406 L I only want to change the 0502 part to 0706 in all the cells. Tried using a macro to select only part of it but it changes the other data parts also. I tried the code below but then it doesn't work for 145-P13-0502 L where we have 2 digits after the P. Dim StartChar As Integer Dim EndChar As Integer StartChar = 8 EndChar = 3 ActiveCell.Characters(StartChar, EndChar).Insert (206) Please help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to edit part of a cell
try
Dim pos1 As Integer Dim thisCell as Range For Each thisCell in Selection.Cells pos1 = InStr(thisCell.Value, "-") pos1 = InStr(pos1 + 1, thisCell.Value, "-") thisCell.Characters(pos1 + 1, 4).Insert "0706" Next thisCell wrote in message oups.com... Thanks Fred, it works!! :-) Is it possible to select a few and edit at once or do I have to change them one by one. Thanks heaps, CC Fred wrote: try: Dim pos1 As Integer pos1 = InStr(ActiveCell.Value, "-") pos1 = InStr(pos1 + 1, ActiveCell.Value, "-") ActiveCell.Characters(pos1 + 1, 4).Insert "0706" Good luck Fred wrote in message oups.com... Am trying to edit data in the worksheet where I have to edit only part of the text in the cell - using a macro. 146-P1-0502 L 145-P13-0502 L 145-P8-0403 L 145-P9-0504 L 145-P10-0406 L I only want to change the 0502 part to 0706 in all the cells. Tried using a macro to select only part of it but it changes the other data parts also. I tried the code below but then it doesn't work for 145-P13-0502 L where we have 2 digits after the P. Dim StartChar As Integer Dim EndChar As Integer StartChar = 8 EndChar = 3 ActiveCell.Characters(StartChar, EndChar).Insert (206) Please help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using macro to copy a part of a cell content to next cell | Excel Discussion (Misc queries) | |||
I cannot edit cell format in Excel 2000 (Part of office 2000)! | Excel Discussion (Misc queries) | |||
Can you make a cell with a diagonal, so that you can edit the upper and lower part of the cell? | New Users to Excel | |||
How do I edit a formula as part of a macro? | Excel Discussion (Misc queries) | |||
Edit Macro for GOTO Part No. | Excel Programming |