Excel macro to multiply the conects of a column by 1

Posted on 2011-10-28
Medium Priority
Last Modified: 2012-08-14
I want to convert the contents of a column to numbers by a special Paste > Multiply by 1.
Currently the macro copies the cell Z1 (which contains "1") and does the Special Paste. Is there a way I can do this without copying the 1 from a cell contents?
Question by:RTsal
  • 2
LVL 29

Accepted Solution

leonstryker earned 2000 total points
ID: 37045209
Substitute the range for ActiveCell

ActiveCell.FormulaR1C1 = ActiveCell.Value

LVL 26

Expert Comment

ID: 37045388
Hi, RTsal.

The following code put a value (3.1415926 in this case - just change it to 1) in the clipboard without needing a cell.
Sub Clipboard_Demo()
' Needs a reference to "Microsoft Forms 2.0 Object Library"
Dim xDataObject As dataObject

Set xDataObject = New dataObject
xDataObject.SetText 3.1415926, 1
Set xDataObject = Nothing

End Sub

Open in new window

Please note that a reference to "Microsoft Forms 2.0 Object Library" is required. Let me know if you need to know how to do that.

LVL 50
ID: 37419420
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
LVL 26

Expert Comment

ID: 37395261
teylyn, agreed.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question