Multiply Array by a constant

[cogent]

Hello

I have an array of important numbers. I want to convert every value in this
array to its minus value such that:

Array(i,j) = -Array(i,j)

Do I have to do this as a loop or can I multiply somehow by a constant?
Please note that every other row contains text values only.

Thank you.

W

 

[Frank Kabel]

Hi
to my knowledge you have to loop through your array (and check for a
numeric value in doing this)

 

[cogent]

Yes, I might have to do the loop then. But I was remembering a rule of
thumb that if it is possible to use a Excel object it can execute quickly.

The only shorter way (instead of looping) I could think of was:

Range("Array").Copy
Range("NewArray").PasteSpecial Paste:=xlPasteValues
Range("NewArray").PasteSpecial Paste:=xlPasteValues,
Operation:=xlSubtract
Range("NewArray").PasteSpecial Paste:=xlPasteValues,
Operation:=xlSubtract

The destination was a spreadsheet anyway so the Operation feature under
PasteSpecial accomplishes the goal, neatly ignoring the text because of the
first paste of "values" without the operation. Is the above "bad code"?
Would I be better off using a loop?

Thank you

W

 

[Dave Peterson]

If the destination will be a worksheet, then you could paste the values once.

then put -1 in a cell.
Copy it.
pastespecial (to your new range) multiply.
then clean up that helper cell.