Using a macro to edit part of a cell

  • Thread starter Thread starter ccholai
  • Start date Start date
C

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.
 
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
 
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
 
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
 
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
 
Back
Top