Macro or VBA to insert text into a cell.

[bowlesj]

Hi,

I know VBA for MS-access on a close to expert level (over 15 years experience). However I am very poor at VBA for excel (basically rank beginner).

I am trying to insert select text strings at the front of cells using a macro. These are to categorize to-do list entries before I sort them. For example "Weekend: ", "Speed Up: ", etc. The idea is to have a few macros I can run for each category to save the typing. I can't figure it out.

Things I have tried :

1/ I tried simply recording the macro but once done if I run the macro it will copy the whole prior cell onto the next cell rather than insert the desired text.

1/ I tried to create a macro which uses the command SendKeys "{F2}", true followed by additional SendKeys commands to insert the text string "Weekend: " in the front portion of a cell. However the {F2} does not get recognized. I gather you can not use sendkeys once a cell is opened for edit.

I am very tempted to do my usual routine of spending 8 hours figuring it out (did that with a re-sequence loop for reassigning priority sequence numbers to the to-do list and finally got it to work). However I can not afford that at the moment and I suspect it can be done fairly easily.

Thanks for any pointers,
John.


By the way, this is my resequence macro code. How do I get it to properly format like code? I do not see a code button in the forum.


Sub ReSeq()
MsgBox "You can not execute this with Ctrl+R because it screws up the MicroSoft sendkey commands that the script uses and the marco does not work"
SendKeys "^{HOME}", True
X = 0
Y = 9000
Do While X < 300
ActiveCell.Value = Y
X = X + 1
Y = Y - 10
SendKeys "{DOWN}", True
Loop
SendKeys "^{HOME}", True
End Sub

 

[VoG]

That would be like this

Sub ReSeq()
X = 0
y = 9000
Do While X < 300
    Cells(y, X).Value = "Weekend: " & Cells(y, X).Value
    X = X + 1
    y = y - 10
Loop
End Sub

To use code tags:

[code]
your code here
[/code]

 

[bowlesj]

HI HTH, Peter,

Thanks for the reply.

I like you suggestion to make a copy before testing. LOL, for me it is wise.

I went searching and read one example, created a basic macro and adjusted it to this. It basically works.

Sub Weekend()
    ActiveCell.FormulaR1C1 = "Weekend: " & ActiveCell.Value
End Sub

I guess I just figured it out (Bad estimate on the 8 hours). You can tell I am a beginner when my estimate on this is out by 7.5 hours. LOL.

Sub Weekend()
    ActiveCell.FormulaR1C1 = "Weekend: " & ActiveCell.Value
    With ActiveCell.Characters(Start:=1, Length:=8).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleSingle
        .ColorIndex = 1
    End With
    With ActiveCell.Characters(Start:=9, Length:=8).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
End Sub

 

[VoG]

Try this

Sub Weekend()
    With ActiveCell
        .Value = "Weekend: " & .Value
        With .Characters(1, 8).Font
            .Bold = True
            .Underline = True
        End With
    End With
End Sub

 

[bowlesj]

Hi Peter,

It worked perfectly. Much simpler code.

Thanks again,
John