XL VBA - Paste Values and preface the pasting of Values by an equal sign

[2Took]

Below code pastes, but I need it to Paste Values and preface the pasting of Values by an equal sign:

Sub Paste()
'Tools -> References -> Microsoft Forms 2.0 Object Library
'or you will get a "Compile error: user-defined type not defined"
  Dim DataObj As New MSForms.DataObject
  Dim S As String
  DataObj.GetFromClipboard
  S = DataObj.GetText
  'Debug.Print S 'print code in the Intermediate box in the Macro editor
  Target.Value = S
End Sub

 

[2Took]

You need two lines of code to copy and paste values using the code I posted:

S=cells(10,10).value   ' this line copies the values from the cell in row 10 column 10 into a variant variable called S
target.value =s      ' this line of code copies the value in variant S into the cell given by the target address.

You didn't state where you were copying from so I assumed row 10 column 10 Very unlikely to be correct) but it was just to show you.
which cells is your substitute function in?? and where do you want the results??

Hi @offthelip,

Currently I have a BeforeRightClick Worksheet event that runs Selection.Copy, so that's how I copy.
For this particular scenario, I am open to change the code that is triggered by BeforeRightClick because I know I'll be copying from A2.
For pasting I need for the target to be whatever cell I pick, can't be hardcoded.

I'm copying a formula from A2, e.g.:
=SUBSTITUTE(A4,"H",A1)
which has a value of below, when A1 contains "G", because it edits a formula in A4:

"G" & ". " & G10 & " - " & INDIRECT("G"&L1)

And I want to be able to paste the above line, but prefaced by an equal sign, so it becomes another formula, like this:
="G" & ". " & G10 & " - " & INDIRECT("G"&L1)

• Let me know if this leaves out any of the questions you might have.

Can you give the entire code needed to go with your suggested code below?

S = Cells(2,1).Value 'A2 to copy from
Target.Value = S

 

[offthelip]

It is very easy to do this using a varaint array to hold the string between the copy and the paste.
Put this code in the worksheet module:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Call copytovariant(Target)
End Sub

Then put this code into a standard module. Note the declaration of singlevariant must be done right at the top of the module:

Public singlevariant As Variant
Sub copytovariant(trange As Range)
singlevariant = trange.Value
End Sub

Sub Pastevariant()
ActiveCell = singlevariant
End Sub

To do the paste you need to run the pastevariant code, I am not sure how you are triggering that.

 

[2Took]

It is very easy to do this using a varaint array to hold the string between the copy and the paste.
Put this code in the worksheet module:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Call copytovariant(Target)
End Sub

Then put this code into a standard module. Note the declaration of singlevariant must be done right at the top of the module:

Public singlevariant As Variant
Sub copytovariant(trange As Range)
singlevariant = trange.Value
End Sub

Sub Pastevariant()
ActiveCell = singlevariant
End Sub

To do the paste you need to run the pastevariant code, I am not sure how you are triggering that.

Awesome solution!

I just added the "=" & into below part, and now I have what I was looking for, Thank You!

Sub Pastevariant()
ActiveCell = "=" & singlevariant
End Sub

... and I'm triggering that by Worksheet_BeforeDoubleClick