sendkeys problem

[vcllvc]

Hi,there,
I try to use a marco in Excel, but it has some problem.
I make a marco to do the following keystrokes:
F2, shift "HOME", ctrl X, "enter"
basically, try to cut the content within a cell.
ok, it works when I run this from choosing it from the menu. However, when I use the assigned macro shortkey, it do something else.

Please help...
Thanks in advance

Vincent.

 

[Zathras]

That's not how VBA works. Use this instead:
[blue]

   Range("B2").Cut

[/color]
Look in the help file for the Range Object and study the methods available.

 

[vcllvc]

my problem isn't just copy and paste.
I want to copy the content and then append to another cell.
Does anyone have a idea?
Thanks

Vincent

 

[Zathras]

It would be much easier to help you if you could describe what you are really trying to do.

Based on what you have said so far, this meets the specs, but there may be yet better ways to do what is really needed.
[blue]

Sub test()
  Range("B1") = Range("B1") & Range("A1")
  Range("A1").Clear
End Sub

[/color]

 

[vcllvc]

Thank a lot. It works. Thank you.

The help guide from excel is confusing, just my opinion.
Is there any other place I can get this kind of information more clearly? Thanks.

Vincent.

 

[vcllvc]

Hay, Zathras

Thanks for your help, first. Your solution help me a lot, but I want to solve the problem dynamically. Let say I am at "B145" cell,where it can be cell in fact, I want to join this cell to Column A. I try the folowing, but I get an error: run-time error called method 'range' of object '_Global' failed.

Can someone help me ?

sub test()
currentRow = ActiveWindow.RangeSelection.row
currentcolumn = ActiveWindow.RangeSelection.Column
Range(Cells(currentRow, currentcolumn - 1)) = Range(Cells(currentRow, currentcolumn - 1)) & Range(Selection)

end sub

 

[Zathras]

Vincent, I agree that the help can be confusing, but if you stick with it, it will eventually become helpful. Did you look up "RangeSelection"? Why are you using that instead of "Range" (Just curious.)

As for your code, did you lookup "Cells" in the help file? If so, you should have learned that the Cells property returns a range. Did you look up the Range method in the help file? If so, you should have learned that the Range method takes a string as its argument. (Microsoft uses the term "argument" for what some languages call a "parameter" -- I prefer the term "parameter" but when in Rome... So let's not get into a discussion of semantics.) Anyway, putting those two things together should indicate why the expression Range(Cells(xxx)) would produce an error, because you are trying to pass a range argument when a string is expected. To make your code work, just remove Range( ) from around everything in the offending statement.

Now, if you really want to be a programmer, you should put this statement at the top of every code module:
[blue]

     Option Explicit

[/color]

Here is a suggested solution that a professional programmer might use (Note that variables are not required when using an object-oriented approach. Note also that most of the code is for error detection and handling, and half of the source lines are comments. The actual work is done in one line of code.):
[blue]

Option Explicit

Sub test()
  ConcatenateLeft Selection
End Sub

Sub ConcatenateLeft(ACellRef As Range)

[green]

' Purpose:
'   Update the contents of the cell to the left
'   of the referenced cell by appending the
'   contents of the referenced cell to it.
'   The referenced cell must be a single cell.
'   and must be in column "B" or higher
' Requestor:
'   Response to Tek-tips post - see thread 707-626254
' Side effects:
'   If the cell to the left has a formula, it will be
'   converted to text, and the formula will be lost.
' Modifications (date - changed by - comments):
'   8/11/2003 - Zathras - Author.

[/color]

  If ACellRef.Cells.Count = 1 Then
    If ACellRef.Column >= 2 Then
      ACellRef.Offset(0, -1) = ACellRef.Offset(0, -1) & ACellRef
    Else
      MsgBox "The referenced cell must be in column 'B' or higher."
    End If
  Else
    MsgBox "The referenced cell must be a single cell."
  End If
End Sub

[/color]

 

[vcllvc]

Thanks for your info.

Here is my solution.

sub test()
currentrow = ActiveWindow.RangeSelection.row
currentcol = ActiveWindow.RangeSelection.Column
Cells(currentrow, currentcol - 1) = Cells(currentrow, currentcol - 1) & Cells(currentrow, currentcol)
Cells(currentrow, currentcol).Clear
end sub

Thanks a lot..

Vincent