Paste_Special with a Twist

J

JMay

I wish to create a tool (Macro) where I select numeric cell values on a
worksheet (whether constants or formula-based) either contiguous or
non-contiguous (never over say, 20 cells) Copy them (Control-C) then click
on a destination cell D1 and **at this moment** click a special toolbar
button which runs code to paste in:
=123+456+789+100+200+300 >>> whereby D1 displays 1968 (the total) of
course. Whatever's in the clipboard (ever how it is laid-out) needs to me
edited to receive the beginning "=" and the insertion of the "+"'s << a bit
tricky for me at this point. Clearing the Clipboard at he end would be the
final step. Can anyone assist me in the development?
TIA,
 
B

BrianB

Easy peasy. Forget clipboard. No need to copy. In this case, once again
Excel's ability to automatically convert numbers to text (or vic
versa) saves some hassle.

'---------------------------------
Sub SelectionTotal()
Dim MyFormula As String
MyFormula = "="
For Each c In Selection.Cells
MyFormula = MyFormula & "+" & c.Value
Next
ActiveSheet.Range("D1").Value = MyFormula
End Sub
'----------------------------------
 
B

BrianB

Version 2 with a more elegant way of handling negative values :-

'--------------------------------------------------------------
Sub SelectionTotal()
Dim MyFormula As String
MyFormula = "="
For Each c In Selection
v = c.Value
MyFormula = MyFormula & IIf(Sgn(v) = -1, "-", "+") & Abs(v)
Next
ActiveSheet.Range("D1").Value = MyFormula
End Sub
'-------------------------------------------------------------
 
J

JMay

BrianB, this is GREAT.
This gets me so close. Only thing is we have Fixed the return variable to
D1;
I sorta need to be able to "Paste" the Variable MyFormula to another
designated or selected cell by pointing and clicking. But how?
Tks for your help with this,
JMay
 
S

Stephen Bye

You could follow it with a Range("D1").Copy to put the formula onto the
clipboard ready for you to paste.

JMay said:
BrianB, this is GREAT.
This gets me so close. Only thing is we have Fixed the return variable to
D1;
I sorta need to be able to "Paste" the Variable MyFormula to another
designated or selected cell by pointing and clicking. But how?
Tks for your help with this,
JMay
Click to expand...
 
J

JMay

Thanks Stephen; Good idea - tried it and it works (only I used Cut versus
Copy)
since I really want to avoid even temporarily going to my sheet with the
String as it might overwrite a possible value already in D1 - but if I have
to the cut at least "removes" the value in the process. Is there a
workaround to achieve?
Appreciation here..


Stephen Bye said:
You could follow it with a Range("D1").Copy to put the formula onto the
clipboard ready for you to paste.
Click to expand...
 
S

Stephen Bye

Then instead of putting it into D1 and copying it out of there, use a data
object like this (untested):
Dim store As DataObject
store.SetText (MyFormula)
store.PutInClipboard
 
J

JMay

My current code; Crash occurring at line 9:
Sub CellsValuesInTotal()
Dim MyFormula As String
Dim store As Object <<< I removed the word "Data" before
Object
MyFormula = "="
For Each c In Selection
v = c.Value
MyFormula = MyFormula & IIf(Sgn(v) = -1, "-", "+") & Abs(v)
Next
store.SetText (MyFormula) <<<< Currently Crashing Here !!
store.PutInClipboard
End Sub

See my problem?
 
S

Stephen Bye

Hmm...
I see that DataObject is only allowed on a UserForm, and PutInClipboard
doesn't work with Object.
I'll see if I can find another way to get the string onto the clipboard, but
you will probably have to find a spare cell to store the result in for now.
 
J

Jim Rech

I think you're on the right track, Stephen. This worked fine for me.

Sub CellsValuesInTotal()
Dim MyFormula As String
Dim Cell As Range, CellVal As String
Dim Store As DataObject
MyFormula = "="
For Each Cell In Selection
CellVal = Cell.Value
MyFormula = MyFormula & IIf(Sgn(CellVal) = -1, "-", "+") &
Abs(CellVal)
Next
Set Store = New DataObject
Store.SetText MyFormula
Store.PutInClipboard
End Sub

Leave DataObject as is, JMay<g>. If it's a problem make sire you have a
Tools, reference set to the Microsoft Forms 2.0 Object Library.
 
J

JMay

Jim,
That did it; perhaps it was the reference issue, which I didn't have checked
(but now do). Thanks so much to you and to Stephen as well.
JMay
 
Top