Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Renaming tabs from particular cell contents | Excel Worksheet Functions | |||
vb code for renaming a work sheet with a cell reference | Excel Discussion (Misc queries) | |||
Maintain cell links when renaming directory containing multiple f | Excel Worksheet Functions | |||
Renaming a cell name | Excel Programming | |||
Renaming Text Boxes and Buttons | Excel Programming |