Using a macro to edit part of a cell

[ccholai]

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

 

[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

 

[ccholai]

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

 

[Gary Keramidas]

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

 

[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