Using a macro to edit part of a cell

[ccholai@gmail.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.

[Fred]

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

<ccholai@gmail.com> wrote in message
news:1155004789.736303.301260@m73g2000cwd.googlegroups.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.
>

[Gary Keramidas]

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


<ccholai@gmail.com> wrote in message
news:1155004789.736303.301260@m73g2000cwd.googlegroups.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.
>

[ccholai@gmail.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
>
> <ccholai@gmail.com> wrote in message
> news:1155004789.736303.301260@m73g2000cwd.googlegroups.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.
> >

[Gary Keramidas]

change the range in my example and see if it does what you want.

--


Gary


<ccholai@gmail.com> wrote in message
news:1155008933.623475.123580@i42g2000cwa.googlegroups.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
>>
>> <ccholai@gmail.com> wrote in message
>> news:1155004789.736303.301260@m73g2000cwd.googlegroups.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.
>> >
>

[Fred]

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


<ccholai@gmail.com> wrote in message
news:1155008933.623475.123580@i42g2000cwa.googlegroups.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
>>
>> <ccholai@gmail.com> wrote in message
>> news:1155004789.736303.301260@m73g2000cwd.googlegroups.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.
>> >
>