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

 

[offthelip]

Target.Value = "=" & S

 

[2Took]

Target.Value = "=" & S

I tried that before posting my question, but it doesn't work.

Maybe it has something to do with, that I'm copying something of this nature:
=SUBSTITUTE(A4,"H",A1)
which has a value of below, 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:
="G" & ". " & G10 & " - " & INDIRECT("G"&L1)

 

[offthelip]

It what way doesn't it work?? You could try wrapping brackets around it:

Target.Value = "=(" & S & ")"

 

[2Took]

It what way doesn't it work?? You could try wrapping brackets around it:

Target.Value = "=(" & S & ")"

It does not work, as in either version pastes:
=SUBSTITUTE(H10,"H",H7)

 

[offthelip]

that means that S = SUBSTITUTE(H10,"H",H7)
NOT the string that you want. you need to go back to where you do the copy to get the string on the clipboard to values Not the formula

What does the rest of the code look like? do you Need to do it via the clipboard.
I do copy of values like this:

S=cells(10,10).value
target.value=S

 

[2Took]

that means that S = SUBSTITUTE(H10,"H",H7)
NOT the string that you want. you need to go back to where you do the copy to get the string on the clipboard to values Not the formula

What does the rest of the code look like? do you Need to do it via the clipboard.
I do copy of values like this:

S=cells(10,10).value
target.value=S

What is this?

cells(10,10)

I modified it like this:

S = ActiveCell.Value

but it does not copy anything

I used your line above as a standalone, should that be part of something else?

The rest of my code is in post #1: XL VBA - Paste Values and preface the pasting of Values by an equal sign

 

[offthelip]

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

 

[offthelip]

Note: The rest of your code is NOT in post #1.
Firstly. In particular there is no code which is copying anything onto the clip board.
Secondly Target is undefined in your code. so unless it is defined correctly your code won't work ( it may be defined correctly somewhere else in the code you haven't posted)
You could post more of your code and also explain what you are trying to do, it might allow us to help you
finally copying and pasting via the clipboard is not usually a good way of copying and pasting with vba

 

[iggydarsa]

Target.Formula= "=(" & S & ")"