Paste Special +VBA

[davetek]

hi,

i recored a macro that multiplies a column by 1. this macro simply copies a cell containing the digit 1 and uses the multiply option in the paste special tool.
however i want to hard code the digit 1 to vba code so as to avoid the selection.copy
any ideas...

below is the code i am currently using:

Range("I1&quot.Select
Selection.Copy
Range("F2&quot.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlMultiply, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False


cheers...

 

[xlbo]

The only way you can simplify this is by using Range("I1&quot.copy
rather then select and then copy
To use the pastespecial method, it must pick up a range value

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[ssVBAdev]

Why would you want to do this in the first place? Multiplying the values by 1 will return the same values. It just seems like redundant code. But, you wouldn't be able to "hard code" this proceedure using the .pastespecial method. You would need to write a For... Next Loop to cycle through each cell in the range that you want to multilpy by 1.

 

[xlbo]

exactly - to be able to paste, you must first copy

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[davetek]

the problem is that i am importing a data set from a database which contains both text and numbers...the format function in excel will not allow me to format the numbers.
the only way i can format the numbers is by first multiplying them by 1. they are then recognised as a number and hence excel will allow formatting...

 

[ssVBAdev]

What if you used the Cdbl() or Cint() Functions to force the value to a double or long data type?