Recorded macro has hard cell contents.

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

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


--
HTH,
Bernie
MS Excel MVP


"DocBrown" <DocBrown@discussions.microsoft.com> wrote in message
news:8B267CF4-4B99-4E81-B579-433F08040714@microsoft.com...
> 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

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


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%23X0EnpWvGHA.1808@TK2MSFTNGP06.phx.gbl...
> 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
>
>
> --
> HTH,
> Bernie
> MS Excel MVP
>
>
> "DocBrown" <DocBrown@discussions.microsoft.com> wrote in message
> news:8B267CF4-4B99-4E81-B579-433F08040714@microsoft.com...
>> 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
>
>

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

"Bernie Deitrick" wrote:

> 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
>
>
> --
> HTH,
> Bernie
> MS Excel MVP
>
>
> "DocBrown" <DocBrown@discussions.microsoft.com> wrote in message
> news:8B267CF4-4B99-4E81-B579-433F08040714@microsoft.com...
> > 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
>
>
>

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


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