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
