multiply value with 1 - optimization

[danovkos]

hi all,
How can i optimized this code?
it multiply selected area with 1 (results is, that all values are numbers)
this code works, but it takes a lot of time if is selected bigger area .

Sub text_to_number()
Const erate = 1
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
    If IsNumeric(cell.Value) And LenB(cell.Value) Then cell.Value = cell.Value * erate
Next cell
Application.ScreenUpdating = True

End Sub

thank you

[Bob Phillips]

[vba]

Sub text_to_number()
Const erate = 1
With Range("IV1")
.Value = erate
.Copy
Selection.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlMultiply
.ClearContents
End With
End Sub
[/vba]

[danovkos]

yes, this works good, but it change format for selected area

[Aussiebear]

What format are you looking for?

[danovkos]

it depends of selected range...the best is, the code will use the same format as selected range
is this possible?

[Aussiebear]

Bob, has supplied code which to my understanding copies the format of the range selected. Note the use of the line

[VBA]Selection.PasteSpecial Paste:=xlPasteAll[/VBA]

Excel help suggests that when using Paste Special, if you wish to copy and paste all of the cells contents including the format of the cells, then you need to use the ALL option as in Paste:=xlPasteAll.

However from your replies I'm of the belief that you might be wanting to copy a range of cells and have them comply with the target cells format. Is this correct?

[Bob Phillips]

No Bear, it copies the format of the cell that the 1 is created in, so it does override any special formatting.

[Aussiebear]

Okay I stand corrected.

[Aussiebear]

Copy specific cell contents or attributes in a worksheet

You can copy and paste specific cell contents or attributes (such as formulas, formats, or comments) from the Clipboard in a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) by using the Paste Special command.

• On a worksheet, select the cells that contain the data or attributes that you want to copy.
• On the Home tab, in the Clipboard group, click Copy

Keyboard shortcut You can also press CTRL+C.

• Select the upper-left cell of the paste area (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.).

Tip To move or copy a selection to a different worksheet or workbook, click another worksheet tab or switch to another workbook, and then select the upper-left cell of the paste area.

• On the Home tab, in the Clipboard group, click Paste , and then click Paste Special.

Keyboard shortcut You can also press CTRL+ALT+V.

• In the Paste Special dialog box, under Paste, do one of the following:
  • To paste static data, click the attribute of the copied data that you want.

Click this option
To
All
Paste all cell contents and formatting.
Formulas
Paste only the formulas as entered in the formula bar.
Values
Paste only the values as displayed in the cells.
Formats
Paste only cell formatting.
Comments
Paste only comments attached to the cell.
Validation
Paste data validation rules for the copied cells to the paste area.
All using Source theme
Paste all cell contents and formatting using the theme that was applied to the source data.
All except borders
Paste all cell contents in the document theme formatting that is applied to the copied data.
Column widths
Paste the width of one column or range of columns to another column or range of columns.
Formulas and number formats
Paste only formulas and number formatting options from the selected cells.
Values and number formats
Paste only values and number formatting options from the selected cells.

• • To paste linked data, click All or All except borders.
• Do any of the following as needed:
  • To mathematically combine the contents of the copy area (copy area: The cells that you copy when you want to paste data into another location. After you copy cells, a moving border appears around them to indicate that they've been copied.) with the contents of the paste area, under Operation, specify the mathematical operation that you want to apply to the copied data.

Click this option
To
None
Paste the contents of the copy area without a mathematical operation.
Add
Add the values in the copy area to the values in the paste area.
Subtract
Subtract the values in the copy area from the values in the paste area.
Multiply
Multiply the values in the paste area by the values in the copy area.
Divide
Divide the values in the paste area by the values in the copy area.

• • Note Mathematical operations can be applied only to values. To use an option other than None, you must select All, Values, All except border, or Values and number formats under Paste.
  • To avoid replacing values in your paste area when blank cells occur in the copy area, select Skip blanks.
  • To change columns of copied data to rows, or vice versa, select Transpose.

Note Depending on the type of data that you copy and the Paste options that you select, specific options may be unavailable.

• If you want to link the pasted data to the original data, click Paste Link.

[danovkos]

I dont understund why, you post me this text. I know how can i paste special and i know to use it. But what i hoped was, that it is possibility to do this without creating any number in free cell and then copying it to selected area as multiply or devide. Mostly i work with huge table where is no direct place, where can i write my number (number to deviding or multiplying). But if it is too hard to do this, OK. Its shame.
but thx.