CODEWITHSUNDEEP
excelvba
Cristiano Morresi
Cristiano Morresi

Reputation: 53

vba paste a value copied into a variable (application.onkey)

i ' ll explain what my problem is, what i want to get and how i would like to do but i can't.

So first here what i need,

i have some sort of calendar with some dates in it like this: [1]: https://i.sstatic.net/w7NLO.png

what i want to do is copy a value(string) in another cell but if in the other cell there is a value(string), the value in it and the value copied before they concatenate each other strings, something like that:

enter image description here

reading on internet i found what i need but i can't make it work, i would like to pass the value copied with ctrl+c in to a variable and then with a custom shortcut paste in to another cell and if in the cell there is something they concatenate each other strings, like the picture above

i thought a code like this, but it does not work obviusly

    Sub g()
    Application.OnKey "^e", "test"

     End Sub


  Sub TEST()
 Dim l As String
  l = PasteSpecial

  If ActiveCell.Value <> "" Then
       ActiveCell.Value& " " & l
   ElseIf ActiveCell.Value = "" Then
         ActiveCell.Value = l
  End If


  End Sub

i know it looks stupid what i wrote but i don't know how onkey work

thanks in advance

Cristiano

Upvotes: 0

Views: 807

Answers (2)

Cristiano Morresi
Cristiano Morresi

Reputation: 53

finally i did it

this is the solution that i found thanks to @faneduru suggestion

Sub TEST()

    Dim l As String
      l = ActiveCell.Value

    If ActiveCell.Value = "" Then
       ActiveCell.PasteSpecial xlvalue
    Else
       ActiveCell.PasteSpecial Paste:=xlvalue
       ActiveCell.Value = l & ActiveCell.Value
    End If

End Sub

Upvotes: 0

FaneDuru
FaneDuru

Reputation: 42256

Please, try the next testing code. It can be adapted to work on a range iteration:

Sub TEST()
 Dim l As String
  l = "My String"

  ActiveCell.value = ActiveCell.value & IIf(ActiveCell.value = "", "", " + ") & l
End Sub

Or taking the value from another cell:

Sub TEST()
 Dim l As String
  l = ActiveCell.Offset(0, 2).Value 'the value in the second column refering the active cell

  ActiveCell.value = ActiveCell.value & IIf(ActiveCell.value = "", "", " + ") & l
End Sub

Edited:

My last attempt:

Sub testCopyfromTheSameRow()
  Dim ICol As Long
  
  ICol = 3 'The column number where the value to be copied
   ActiveCell.value = ActiveCell.value & ActiveSheet.cells(ActiveCell.row, ICol).value

End Sub

Upvotes: 1

Related Questions