Solved: how does xlMultiply work? and can i paste and multipy in one go?

[AlexMason]

Hey guys

Sheets("Sheet1").Select
Sheets("Sheet1").Cells(Copy_Row, Copy_Col).Select
ActiveCell.Copy
Sheets("HP").Select
Sheets("HP").Cells(Paste_Row, Paste_Col).Select
Selection.PasteSpecial Paste:=xlPasteValues

thats a snippet of my code

what i want to be able to do on that last line with the paste special is multiply it by a variable which has been determined earlier on depending on whether metric or imperial units where chosen in the form.

basically its taking a metric value from one sheet and pasting into another and if metric was chosen i want it multiplied by 1 or if imperial was chosen i need it multiplied by (1/25.4) to go to inches from mm


can that be done? i seen a xlMultiply Operator but i cant really fathom what it does.... where do you put the number you wish to multiply by?

or is it just safer to go with saving the cell contents to a variable, multiply that variable by the conversion factor variable and then putting that back in the cell?

[AlphaFrog]

If you copy the value 1/25.4 to the clipboard, you could then PasteSpecial that value to a cell using xlMultiply. That would multiply the existing value in the cell by 1/25.4 but again you first have to copy the value 1/25.4 to the clipboard for this to work.

An easier way to paste a value and convert it all in one step is to use something like this...
Sheets("HP").Cells(Paste_Row, Paste_Col).Value = Sheets("Sheet1").Cells(Copy_Row, Copy_Col).Value * Convert
Convert would be a variable that you set to 1 if you don't want to convert the value and set to 1/25.4 if you do.

[Bob Phillips]

xlMultiply is a special past case, you can't do both.



With Sheets("HP")

Sheets("Sheet1").Cells(Copy_Row, Copy_Col).Copy
.Cells(Paste_Row, Paste_Col)
.Value = .Value
If Imperial Then '<<<<<<< change to the real test

.Value = .Value / 25.4
End If
End With

[AlexMason]

ok thanks guys, ill give it a try...ill try alphafrogs first as i already performed the imperial/metric test at the start of the code to keep the number of nested if's down later on.

EDIT:

right, AlphaFrogs method works and cuts the code down. cheers!

[Bob Phillips]

ok thanks guys, ill give it a try...ill try alphafrogs first as i already performed the imperial/metric test at the start of the code to keep the number of nested if's down later on.

It isn't a nested if, it is a separate, in-line test. Very different from a readability point.