renaming text in cell
 

Hello hope somebody can help me out with this, driving me nuts. I´m
trying to rename specific cells (which start with the word "Cd" into
CDC Philly, but something is wrong here, pretty sure it´s the
identifier.

Sub TextChange()

Dim rng As Characters
Dim i As Double, counter As Double
Sheets("Retail Raw Data").Select
Set rng = Range("o2:o65000")
i = 1
Set rng = .Search("cd ", LookIn:=x1values)
If Not rng Is Nothing Then
Retail = rng.gildi
Do
rng.Value = "Empty"
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.gildi Is Not "Empty"
End If
End With
For counter = 1 To rng.Rows.Count
If rng.Cells(i) = "Empty" Then
rng.Cells(i).FormulaR1C1 = "CDC Philly"
Else
i = i + 1
End If
Next
End Sub


--
jhkr
------------------------------------------------------------------------
jhkr's Profile: http://www.excelforum.com/member.php...o&userid=24665
View this thread: http://www.excelforum.com/showthread...hreadid=382413

renaming text in cell
 

How about just selecting your range and doing:

Edit|replace
what: CD_ (_ is the spacebar)
with: CDC Philly
replace all.

If you want to replace the contents of the whole cell when it starts with CD_:

Edit|replace
what: CD_* (_ is the spacebar)
with: CDC Philly
replace all.

If you need a macro, record one when you do it manually.

You'll see some significant differences.

search will be replaced with Find and x(one)values will become x(ell)values.




jhkr wrote:

Hello hope somebody can help me out with this, driving me nuts. I´m
trying to rename specific cells (which start with the word "Cd" into
CDC Philly, but something is wrong here, pretty sure it´s the
identifier.

Sub TextChange()

Dim rng As Characters
Dim i As Double, counter As Double
Sheets("Retail Raw Data").Select
Set rng = Range("o2:o65000")
i = 1
Set rng = .Search("cd ", LookIn:=x1values)
If Not rng Is Nothing Then
Retail = rng.gildi
Do
rng.Value = "Empty"
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.gildi Is Not "Empty"
End If
End With
For counter = 1 To rng.Rows.Count
If rng.Cells(i) = "Empty" Then
rng.Cells(i).FormulaR1C1 = "CDC Philly"
Else
i = i + 1
End If
Next
End Sub

--
jhkr
------------------------------------------------------------------------
jhkr's Profile: http://www.excelforum.com/member.php...o&userid=24665
View this thread: http://www.excelforum.com/showthread...hreadid=382413

--

Dave Peterson

renaming text in cell
 

I see a few things that are problematic, plus a one thing I am just confused
about:
1) It should be Dim rng as Range
2) When you have rng.Cells(i).FormulaR1C1 = "CDC Philly" it is setting the
contents of the cell equal to "CDC Philly," not the name. To set the name
use rng.Cells(i).Name = "CDC Philly"
3) I have no clue what a rng.gildi is - I know of no Excel object with a
gildi property.

"jhkr" wrote:


Hello hope somebody can help me out with this, driving me nuts. I´m
trying to rename specific cells (which start with the word "Cd" into
CDC Philly, but something is wrong here, pretty sure it´s the
identifier.

Sub TextChange()

Dim rng As Characters
Dim i As Double, counter As Double
Sheets("Retail Raw Data").Select
Set rng = Range("o2:o65000")
i = 1
Set rng = .Search("cd ", LookIn:=x1values)
If Not rng Is Nothing Then
Retail = rng.gildi
Do
rng.Value = "Empty"
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.gildi Is Not "Empty"
End If
End With
For counter = 1 To rng.Rows.Count
If rng.Cells(i) = "Empty" Then
rng.Cells(i).FormulaR1C1 = "CDC Philly"
Else
i = i + 1
End If
Next
End Sub


--
jhkr
------------------------------------------------------------------------
jhkr's Profile: http://www.excelforum.com/member.php...o&userid=24665
View this thread: http://www.excelforum.com/showthread...hreadid=382413