MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to edit cell data


Posted by Marianne on November 11, 2001 3:12 PM

Hi there,

I am trying to create a macro that will add a word onto the end of the text that is already in the cell. The problem that I am having is that the text in the cell will be different all the time.

For example lets say I had a list of products and wanted to add the word "SOLD" to some of them (in bold preferably).

Television Set
Drum Set
Computer
Printer - SOLD

This is the code that I come up with when I try to record the Macro:

ActiveCell.Select
ActiveCell.FormulaR1C1 = "Printer - SOLD"
Range("B8").Select

The problem is that because the formula states "Printer..." that is all I get. I want the text to stay there and only add the word "SOLD." Also, what would be the code to use so that I don't always end up on cell "B8?" I want to stay where I am at.

Thanks,

Marianne


Posted by Tom Urtis on November 11, 2001 6:34 PM

Here's the code I think you are requesting:

Sub EditCell()
ActiveCell.Value = ActiveCell.Value & " - ''SOLD''"
ActiveCell.Font.Bold = True
End Sub

I don't understand your final request about not ending up in cell B8. Your active cell is where you are, which is where the commodity is listed that got sold. I assume you are noting items being "Sold" as they get sold, which means your active cell approach is probably a good one. So the question is, where do you want to end up?

Tom Urtis

Posted by Marianne on November 11, 2001 6:41 PM

A Portion of question solved. Still need help though.

Okay, I figured out how to get the "SOLD" to appear next to the text, but I would still like to get it to be bold. This is the new code that I have and after it I will explain the problem that I am having.

ActiveCell.Select
ActiveCell.FormulaR1C1 = ActiveCell + " - SOLD"
With ActiveCell.Characters(Start:=11, Length:=4).Font
.FontStyle = "Bold"
End With
Range("B8").Select
End Sub

The problem is that the bold always starts on the 11th character. Is there a way that I can tell it to start at the end of the text and bold backward for 4 spaces? BTW, I figured out how to get it to stay on the same line when I am done.

Thanks,

Marianne

Posted by Marianne on November 11, 2001 6:42 PM

A Portion of question solved. Still need help though.

Okay, I figured out how to get the "SOLD" to appear next to the text, but I would still like to get it to be bold. This is the new code that I have and after it I will explain the problem that I am having.

ActiveCell.Select
ActiveCell.FormulaR1C1 = ActiveCell + " - SOLD"
With ActiveCell.Characters(Start:=11, Length:=4).Font
.FontStyle = "Bold"
End With
End Sub

The problem is that the bold always starts on the 11th character. Is there a way that I can tell it to start at the end of the text and bold backward for 4 spaces? BTW, I figured out how to get it to stay on the same line when I am done.

Thanks,

Marianne

Posted by Tom Urtis on November 11, 2001 7:32 PM

Try this

Try this code:

Sub EditCell()
With ActiveCell
.Value = ActiveCell.Value & " - ''SOLD''"
.Font.Bold = True
.Characters(1, (ActiveCell.Characters.Count - 8)).Font.Bold = False
End With
End Sub


Tom Urtis

Posted by Marianne on November 11, 2001 8:40 PM

It Worked! Thanks so Much :) You are so Smart! NTF