Want macro to select current cell

I want a very simple macro that, when run, will...

Edit the current cell (wherever the user's cellpointer is when the macro is run) - edit being the same as striking F2
Paste
Move down one row

Without a macro, this is simple:
F2
Ctrl-V
Enter

But I want just one keystroke that'll do this.

Why is so difficult to simply tell a macro to edit or select whatever the cell the user is pointing to when they run the macro? All the macro tips I've read and all the help always end up with specific cell addresses.

I really miss the old-time Lotus macros which recorded every single keystroke {LEFT}{DOWN}{END}{RIGHT}, just to give an example.

I digress.

Help.

you may be refering to the activecell.copy and then an
activecell.offset(0,1).paste

"rdaugherty" wrote:

>
> I want a very simple macro that, when run, will...
>
> Edit the current cell (wherever the user's cellpointer is when the
> macro is run) - edit being the same as striking F2
> Paste
> Move down one row
>
> Without a macro, this is simple:
> F2
> Ctrl-V
> Enter
>
> But I want just one keystroke that'll do this.
>
> Why is so difficult to simply tell a macro to edit or select whatever
> the cell the user is pointing to when they run the macro? All the
> macro tips I've read and all the help always end up with specific cell
> addresses.
>
> I really miss the old-time Lotus macros which recorded every single
> keystroke {LEFT}{DOWN}{END}{RIGHT}, just to give an example.
>
> I digress.
>
> Help.
>
>
> --
> rdaugherty
> ------------------------------------------------------------------------
> rdaugherty's Profile: http://www.excelforum.com/member.php...o&userid=28043
> View this thread: http://www.excelforum.com/showthread...hreadid=475515
>
>

Your code is close but there is no paste method here. There is pastespecial.
The code you probably intended is...

Public Sub test()
ActiveCell.Copy ActiveCell.Offset(1, 0)
End Sub

I always thought it might be nice if that method was available...
--
HTH...

Jim Thomlinson


"John" wrote:

> you may be refering to the activecell.copy and then an
> activecell.offset(0,1).paste
>
> "rdaugherty" wrote:
>
> >
> > I want a very simple macro that, when run, will...
> >
> > Edit the current cell (wherever the user's cellpointer is when the
> > macro is run) - edit being the same as striking F2
> > Paste
> > Move down one row
> >
> > Without a macro, this is simple:
> > F2
> > Ctrl-V
> > Enter
> >
> > But I want just one keystroke that'll do this.
> >
> > Why is so difficult to simply tell a macro to edit or select whatever
> > the cell the user is pointing to when they run the macro? All the
> > macro tips I've read and all the help always end up with specific cell
> > addresses.
> >
> > I really miss the old-time Lotus macros which recorded every single
> > keystroke {LEFT}{DOWN}{END}{RIGHT}, just to give an example.
> >
> > I digress.
> >
> > Help.
> >
> >
> > --
> > rdaugherty
> > ------------------------------------------------------------------------
> > rdaugherty's Profile: http://www.excelforum.com/member.php...o&userid=28043
> > View this thread: http://www.excelforum.com/showthread...hreadid=475515
> >
> >

Sorry if I am missing something but do you not just want (assuming that you
have already done the copy just as you would have needed to before doing F2)

Public Sub test()
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
End Sub

"Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
news:6BD3CE15-22D8-4E05-ACE5-1051F6BA0D1D@microsoft.com...
> Your code is close but there is no paste method here. There is
> pastespecial.
> The code you probably intended is...
>
> Public Sub test()
> ActiveCell.Copy ActiveCell.Offset(1, 0)
> End Sub
>
> I always thought it might be nice if that method was available...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "John" wrote:
>
>> you may be refering to the activecell.copy and then an
>> activecell.offset(0,1).paste
>>
>> "rdaugherty" wrote:
>>
>> >
>> > I want a very simple macro that, when run, will...
>> >
>> > Edit the current cell (wherever the user's cellpointer is when the
>> > macro is run) - edit being the same as striking F2
>> > Paste
>> > Move down one row
>> >
>> > Without a macro, this is simple:
>> > F2
>> > Ctrl-V
>> > Enter
>> >
>> > But I want just one keystroke that'll do this.
>> >
>> > Why is so difficult to simply tell a macro to edit or select whatever
>> > the cell the user is pointing to when they run the macro? All the
>> > macro tips I've read and all the help always end up with specific cell
>> > addresses.
>> >
>> > I really miss the old-time Lotus macros which recorded every single
>> > keystroke {LEFT}{DOWN}{END}{RIGHT}, just to give an example.
>> >
>> > I digress.
>> >
>> > Help.
>> >
>> >
>> > --
>> > rdaugherty
>> > ------------------------------------------------------------------------
>> > rdaugherty's Profile:
>> > http://www.excelforum.com/member.php...o&userid=28043
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=475515
>> >
>> >

Hi Mark

I think you are closer to the answer than the previous posts but your
solution will paste over the contents of the activecell whereas the key
strokes F2, Ctrl+V concatenate the text from the clipboard to what is
already in the cell. I don't know how to query the clipboard so this is
pretty messy but maybe like this: (uses column IV and deletes it so as
not to change used range)

Sub AddStuff()
Application.ScreenUpdating = False
Dim aCell As Range
Set aCell = ActiveCell
If Application.WorksheetFunction.CountA(Columns("IV")) = 0 Then
Range("IV1").Activate
ActiveSheet.Paste
aCell.Value = aCell.Value & Range("IV1").Value
Columns("IV").Delete
aCell.Offset(1, 0).Select
End If
Application.ScreenUpdating = True
End Sub

Of course if we knew the OP's intentions there are probably any number
of better solutions. I suspect he/she is wanting to add a string to a
range of cells so maybe something like this would do:

Sub maybe()
Dim addS As String
Dim cell As Range
addS = "xyz"
For Each cell In Range("A1:A20")
cell.Value = cell.Value & addS
Next cell
End Sub

Regards
Rowan


mark walberg wrote:
> Sorry if I am missing something but do you not just want (assuming that you
> have already done the copy just as you would have needed to before doing F2)
>
> Public Sub test()
> ActiveSheet.Paste
> ActiveCell.Offset(1, 0).Select
> End Sub
>
> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> news:6BD3CE15-22D8-4E05-ACE5-1051F6BA0D1D@microsoft.com...
>
>>Your code is close but there is no paste method here. There is
>>pastespecial.
>>The code you probably intended is...
>>
>>Public Sub test()
>> ActiveCell.Copy ActiveCell.Offset(1, 0)
>>End Sub
>>
>>I always thought it might be nice if that method was available...
>>--
>>HTH...
>>
>>Jim Thomlinson
>>
>>
>>"John" wrote:
>>
>>
>>>you may be refering to the activecell.copy and then an
>>>activecell.offset(0,1).paste
>>>
>>>"rdaugherty" wrote:
>>>
>>>
>>>>I want a very simple macro that, when run, will...
>>>>
>>>>Edit the current cell (wherever the user's cellpointer is when the
>>>>macro is run) - edit being the same as striking F2
>>>>Paste
>>>>Move down one row
>>>>
>>>>Without a macro, this is simple:
>>>>F2
>>>>Ctrl-V
>>>>Enter
>>>>
>>>>But I want just one keystroke that'll do this.
>>>>
>>>>Why is so difficult to simply tell a macro to edit or select whatever
>>>>the cell the user is pointing to when they run the macro? All the
>>>>macro tips I've read and all the help always end up with specific cell
>>>>addresses.
>>>>
>>>>I really miss the old-time Lotus macros which recorded every single
>>>>keystroke {LEFT}{DOWN}{END}{RIGHT}, just to give an example.
>>>>
>>>>I digress.
>>>>
>>>>Help.
>>>>
>>>>
>>>>--
>>>>rdaugherty
>>>>------------------------------------------------------------------------
>>>>rdaugherty's Profile:
>>>>http://www.excelforum.com/member.php...o&userid=28043
>>>>View this thread:
>>>>http://www.excelforum.com/showthread...hreadid=475515
>>>>
>>>>
>
>
>

Hi Rowan,

> I don't know how to query the clipboard

See Chip Pearson's invaluable clipboard page at:

http://www.cpearson.com/excel/clipboar.htm


> (uses column IV and deletes it so as not to change used range)

In some versions, simple column deletion would not restore the pre-existing
used range.


---
Regards,
Norman



"Rowan Drummond" <rowanzsaNotThis@hotmail.com> wrote in message
news:%23MHEaP6zFHA.4032@TK2MSFTNGP15.phx.gbl...
> Hi Mark
>
> I think you are closer to the answer than the previous posts but your
> solution will paste over the contents of the activecell whereas the key
> strokes F2, Ctrl+V concatenate the text from the clipboard to what is
> already in the cell. I don't know how to query the clipboard so this is
> pretty messy but maybe like this: (uses column IV and deletes it so as not
> to change used range)
>
> Sub AddStuff()
> Application.ScreenUpdating = False
> Dim aCell As Range
> Set aCell = ActiveCell
> If Application.WorksheetFunction.CountA(Columns("IV")) = 0 Then
> Range("IV1").Activate
> ActiveSheet.Paste
> aCell.Value = aCell.Value & Range("IV1").Value
> Columns("IV").Delete
> aCell.Offset(1, 0).Select
> End If
> Application.ScreenUpdating = True
> End Sub
>
> Of course if we knew the OP's intentions there are probably any number of
> better solutions. I suspect he/she is wanting to add a string to a range
> of cells so maybe something like this would do:
>
> Sub maybe()
> Dim addS As String
> Dim cell As Range
> addS = "xyz"
> For Each cell In Range("A1:A20")
> cell.Value = cell.Value & addS
> Next cell
> End Sub
>
> Regards
> Rowan

Hi R,

Try:
'================>>
Sub Tester()
Dim x As New DataObject
x.GetFromClipboard
ActiveCell.Formula = ActiveCell.Formula _
& Application.Clean(x.GetText)
ActiveCell(2).Select

End Sub
'<<================

This code could be assigned to a button or shortcut key.

---
Regards,
Norman



"rdaugherty" <rdaugherty.1wswmf_1129140329.6616@excelforum-nospam.com> wrote
in message news:rdaugherty.1wswmf_1129140329.6616@excelforum-nospam.com...
>
> I want a very simple macro that, when run, will...
>
> Edit the current cell (wherever the user's cellpointer is when the
> macro is run) - edit being the same as striking F2
> Paste
> Move down one row
>
> Without a macro, this is simple:
> F2
> Ctrl-V
> Enter
>
> But I want just one keystroke that'll do this.
>
> Why is so difficult to simply tell a macro to edit or select whatever
> the cell the user is pointing to when they run the macro? All the
> macro tips I've read and all the help always end up with specific cell
> addresses.
>
> I really miss the old-time Lotus macros which recorded every single
> keystroke {LEFT}{DOWN}{END}{RIGHT}, just to give an example.
>
> I digress.
>
> Help.
>
>
> --
> rdaugherty
> ------------------------------------------------------------------------
> rdaugherty's Profile:
> http://www.excelforum.com/member.php...o&userid=28043
> View this thread: http://www.excelforum.com/showthread...hreadid=475515
>