Recorded macro has hard cell contents.

[Guest]

I've searched this forum but haven't found an answer to this. This should be
a simple task to perform. I'm also not versed in writing Excel macros.

I'm trying to record a macro that will take the contents of a cell and
append it to the contents of another cell in the same row. I've enable
relative addressing so it would work on any row, but the pasted contents is
always the contents of the target cell when the macro was recorded.

The keyboard sequence that works is:

F2; CTRL-SHIFT-HOME; CTRL-C; TAB; TAB; F2;CTRL-V

the macro captured is:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/10/2006 by John E. Spiter
'
' Keyboard Shortcut: Ctrl+w
'
ActiveCell.FormulaR1C1 = ""
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "Brownbag_final_files/slide0194.htm" <---
Note the hard cell contents here….
With ActiveCell.Characters(Start:=1, Length:=34).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(1, -2).Range("A1").Select
End Sub

 

[Bernie Deitrick]

To append the value of the activecell to the value of the cell 2 columns to the right:

ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, 2).Value & ActiveCell.Value

or, if you want an extra space between:

ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, 2).Value & " " & ActiveCell.Value

 

[Bernie Deitrick]

And it appears that you want to delete the activecell's content: so use

ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, 2).Value & ActiveCell.Value
ActiveCell.ClearContents

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks, That worked. Is there a reason the recorded macro didn't work and was
so different?

 

[Bernie Deitrick]

Doc,

Recorded macros are far too literal... they almost always need to be edited, especially when the
cell values are being edited.

HTH,
Bernie
MS Excel MVP