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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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 Norman

>
> See Chip Pearson's invaluable clipboard page at:
>
> http://www.cpearson.com/excel/clipboar.htm

That's just what I was looking for, many thanks.

>
>
>>(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.

I did say it was messy <g>.

Regards
Rowan

The reason why I needed
F2
Ctrl-V (edit/paste)
Enter

was because I had a user that was copying a paragraph of table text from MS Word into cells that were already formatted to Wrap Text. For whatever reason, though, the wrap text feature would turn off and then the cell contents would spread across the page or be covered up.

He had to do this a number of times so I thought I would whip up a quick macro to just hit a keystroke or button. And he couldn't just copy the table into Excel because he was selecting only parts of each MS Word table cell text. And he had to copy into different rows and columns, so he didn't want to copy and paste and then format the whole sheet later to Wrap Text.

Again, I thought it would be easy to create this simple 3-step macro. It should be, but I'm missing a very basic concept I think.

So, yes, I was pasting from the Clipboard.

I tried a few things that were suggested (not the lengthy one, though) but none of them seem to work.

Dim x As New DataObject is not recognized in Excel 2003.

I stopped trying others since some of you may have a better way of doing this.

Hi R,

> Dim x As New DataObject is not recognized in Excel 2003.

My omission!

In the VBE: Tools | References | Locate and check 'Microsoft Forms 2.0
Object Library'

---
Regards,
Norman



"rdaugherty" <[email protected]> wrote
in message news:[email protected]...
>
> The reason why I needed
> F2
> Ctrl-V (edit/paste)
> Enter
>
> was because I had a user that was copying a paragraph of table text
> from MS Word into cells that were already formatted to Wrap Text. For
> whatever reason, though, the wrap text feature would turn off and then
> the cell contents would spread across the page or be covered up.
>
> He had to do this a number of times so I thought I would whip up a
> quick macro to just hit a keystroke or button. And he couldn't just
> copy the table into Excel because he was selecting only parts of each
> MS Word table cell text. And he had to copy into different rows and
> columns, so he didn't want to copy and paste and then format the whole
> sheet later to Wrap Text.
>
> Again, I thought it would be easy to create this simple 3-step macro.
> It should be, but I'm missing a very basic concept I think.
>
> So, yes, I was pasting from the Clipboard.
>
> I tried a few things that were suggested (not the lengthy one, though)
> but none of them seem to work.
>
> Dim x As New DataObject is not recognized in Excel 2003.
>
> I stopped trying others since some of you may have a better way of
> doing this.
>
>
> --
> rdaugherty
> ------------------------------------------------------------------------
> rdaugherty's Profile:
> http://www.excelforum.com/member.php...o&userid=28043
> View this thread: http://www.excelforum.com/showthread...hreadid=475515
>