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

2Took

Board Regular
Joined
Jun 13, 2022
Messages
203
Office Version
  1. 365
Platform
  1. Windows
Below code pastes, but I need it to Paste Values and preface the pasting of Values by an equal sign:


VBA Code:
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
VBA Code:
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)
 
Upvote 0
It what way doesn't it work?? You could try wrapping brackets around it:
VBA Code:
Target.Value = "=(" & S & ")"
 
Upvote 0
It what way doesn't it work?? You could try wrapping brackets around it:
VBA Code:
Target.Value = "=(" & S & ")"

It does not work, as in either version pastes:
=SUBSTITUTE(H10,"H",H7)
 
Upvote 0
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:
VBA Code:
S=cells(10,10).value
target.value=S
 
Upvote 0
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:
VBA Code:
S=cells(10,10).value
target.value=S
What is this?
Code:
cells(10,10)

I modified it like this:
VBA Code:
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
 
Last edited:
Upvote 0
You need two lines of code to copy and paste values using the code I posted:
Excel Formula:
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??
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top