Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

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


Check out our Excel VBA Resources

Re: Macro to edit cell data

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


A Portion of question solved. Still need help though.

Posted by Marianne on November 11, 2001 6:41 PM
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


A Portion of question solved. Still need help though.

Posted by Marianne on November 11, 2001 6:42 PM
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


Try this

Posted by Tom Urtis on November 11, 2001 7:32 PM
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


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

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.