Insert text from clipboard to cell on currently selected row

[genestoy]

I have a worksheet using Excel 2002 that will automatically insert a text string from the clipboard that works but the problem is that I need it to insert the text on the line that I have highlighted and it will only do that on the first line and then it wants to re-write the line above it instead of inserting the text on the line where I want it (may be the second or third line down with a blank line in-between). Here is the Activate & Insert code so maybe someone can tell me what is wrong?
Thanks, Gene

Here is the type of string that will be on the clipboard -- A17; Bill Smith; Mary Smith; Phoenix; AZ; Bigfoot; 21FW; 2003; 8 ; 15; 19; justme@mysite.com ; Joey

 'Summary: Returns text from the clipboard as a string.
 
 'Does the clipboard contain format?
Private Declare Function IsClipboardFormatAvailable _ 
Lib "User32.dll" _ 
(ByVal wFormat As Integer) As Long 
 
 'Open the clipboard
Private Declare Function OpenClipboard _ 
Lib "User32.dll" _ 
(ByVal hwnd As Long) As Long 
 
 'Get a pointer to the formatted data
Private Declare Function GetClipboardData _ 
Lib "User32.dll" _ 
(ByVal wFormat As Integer) As Long 
 
 'Close the clipboard
Private Declare Function CloseClipboard _ 
Lib "User32.dll" () As Long 
 
 'Finds first double Chr$(0) in a string
Private Declare Function lstrlen _ 
Lib "kernel32.dll" _ 
Alias "lstrlenA" _ 
(ByVal lpString As String) As Long 
 
 'Returns a pointer to the clipboard data in memory
Private Declare Function GlobalSize _ 
Lib "kernel32.dll" _ 
(ByVal hMem As Long) As Long 
 
 'Prevent the clipboard data from being overwritten
Private Declare Function GlobalLock _ 
Lib "kernel32.dll" _ 
(ByVal hMem As Long) As Long 
 
 'Free the memory used by the clipboard data
Private Declare Function GlobalUnlock _ 
Lib "kernel32" _ 
(ByVal hMem As Long) As Long 
 
 'Copy clipboard data from protected memory to a string buffer
Private Declare Sub MoveMemory _ 
Lib "kernel32" Alias "RtlMoveMemory" _ 
(ByVal strDest As Any, _ 
ByVal lpSource As Any, _ 
ByVal Length As Long) 
 
 
Function GetClipboardText() As String 
     
    Dim DataSize As Long 
    Dim hClip As Long 
    Dim hData As Long 
    Dim pData As Long 
    Dim Ret As Long 
    Dim strText As String 
     
    Const CF_TEXT As Long = 1& 
     
    Ret = OpenClipboard(0&) 
    If Ret = 0 Then 
        MsgBox "Clipboard is in use." 
        Exit Function 
    End If 
     
    Ret = IsClipboardFormatAvailable(CF_TEXT) 
    If Ret Then 
        hData = GetClipboardData(CF_TEXT) 
        If hData Then 
            DataSize = GlobalSize(hData) 
            strText = Space$(DataSize) 
            pData = GlobalLock(hData) 
            Call MoveMemory(strText, pData, DataSize) 
            strText = Left$(strText, lstrlen(strText)) 
            Call GlobalUnlock(hData) 
        End If 
    Else 
        MsgBox "There is No Text on the Clipboard." 
    End If 
     
    Ret = CloseClipboard 
    GetClipboardText = strText 
     
End Function 

Sub InsertText() 
     
    Dim Rng As Range 
    Dim RngEnd As Range 
    Dim Text As String 
     
    Set Rng = Range("A2:M2") 
    Set RngEnd = Cells(Rows.Count, Rng.Row).End(xlUp) 
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Range(Rng, RngEnd).Offset(1, 0)) 
     
    Text = GetClipboardText 
    If Text <> "" Then Rng.Rows(1).Cells.Value = Split(Text, ";") 
     
End Sub 
 
Sub ActivateInsertKey() 
     
    Application.OnKey "{INSERT}", "InsertText" 
     
End Sub 

[AAE]

Hello genestoy,

Welcome to Ozgrid.

Thread titles are important for obtaining good search results and should be written with this thought in mind -- that is, the title should be "search friendly", meaning a search using YOUR title as the search terms will yield relevant results. Thread titles should concisley and accurately describe the content of your thread.

Your title of "Need macro Help Please" does not adequately describe your thread and is of not help to others searching the forum for a solution to a similar problem.

Click the EDIT POST button below your thread, then click Go Advanced and revise the thread title to something that describes your thread/objective.

Take some time to read the Forum Rules so that you can avoid infractions that may impact your posting privilege. Thanks.

[rikcando]

If you want it to be on the currently selected row then why all the work with Rng and RngEnd? Would not Activecell.Row put you on the highlighted row?

Your example code will only fill (from what I can see) rows 2 and 3 because of the integer value in the offset. If you had used RngEnd-1 instead of the 1 the code would fill the first empty row after 2.

[genestoy]

Thanks for the reply but I am a novice at Excel and did not write the above code so could you explain or show me in the code what needs to be changed?
Thanks for your help
Gene

[rikcando]

If you are looking to have the text placed into the row that you have highlighted, the following code will do that.

Sub InsertText() 
     
    Dim Rng As Range 
    Dim RngEnd As Range 
    Dim Text As String 
     
    Set Rng = Range("A" & ActiveCell.Row & ":M" & ActiveCell.Row) 
     '      Set RngEnd = Cells(Rows.Count, Rng.Row).End(xlUp)
     '      Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Range(Rng, RngEnd).Offset(RngEnd.Row - 1, 0))
     
    Text =GetClipboardText 
    If Text <> "" Then Rng.Rows(1).Cells.Value = Split(Text, ";") 
     
End Sub 

[genestoy]

rikcando,
You are a life saver!!!! That works perfectly and I thank you so much!
Gene

[genestoy]

I need a little help with this again, first it has been working just fine but I now find I need one of the columns (the "I" column) to do a counta which I have already set up but the problem is that the insert above stores the number in the I column as text so the counta won't work with text. How do I make the insert store that one column as a number for the counta? Or if that is too hard to do what code would I use fof that "I" column to total the entries that have numbers in them, not a addition of all the numbers but a total of the cells with numbers in them but excluding any cells with text? Thanks again for any help!

[genestoy]

UPDATE: I figured it out so no help needed, thanks!