Want macro to select current cell

[rdaugherty]

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

Edit the current cell (wherever the user's cellpointer is when th
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 whateve
the cell the user is pointing to when they run the macro? All th
macro tips I've read and all the help always end up with specific cel
addresses.

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

I digress.

Help

 

[Guest]

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

 

[Guest]

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

 

[mark walberg]

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

 

[Rowan Drummond]

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

 

[Norman Jones]

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.

 

[Norman Jones]

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.

 

[Rowan Drummond]

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.

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

I did say it was messy <g>.

Regards
Rowan

 

[rdaugherty]

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

was because I had a user that was copying a paragraph of table tex
from MS Word into cells that were already formatted to Wrap Text. Fo
whatever reason, though, the wrap text feature would turn off and the
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
quick macro to just hit a keystroke or button. And he couldn't jus
copy the table into Excel because he was selecting only parts of eac
MS Word table cell text. And he had to copy into different rows an
columns, so he didn't want to copy and paste and then format the whol
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 o
doing this

 

[Norman Jones]

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'