PDA

View Full Version : Multiply selection by a variable



theta
03-14-2012, 05:15 AM
Hi

I have completed a tool that allows for raw reports to be formatted. I now need the contents of the selection to be multiplied by a value.

What is the best way to do this in VBA?

My selection is Worksheets(1).Range("A1:A50")

Need to mulitply it by the value of 'FX' but have the answer rounded to 4 decimal places....

p45cal
03-14-2012, 06:15 AM
At its simplest from a coding point of view:
Sub blah1()
fx = 23.8
For Each Cll In Worksheets(1).Range("A1:A50")
Cll.Value = Round(Cll.Value * fx, 4)
Next Cll
End Sub This will result in the value in each cell being rounded to 4 decimal places but the unrounded number will be gone. Come back if you only want to see a rounded value and the underlying full accuracy retained behind the scenes.

Although speed probably isn't an issue here, the following is some 160 times quicker (if, say, your range is much bigger):Sub blah2()
fx = 23.8
xxx = Worksheets(1).Range("A1:A50").Value
For i = LBound(xxx) To UBound(xxx)
xxx(i, 1) = Round(xxx(i, 1) * fx, 4)
Next i
Worksheets(1).Range("A1:A50").Value = xxx
End Sub

Kenneth Hobs
03-14-2012, 06:18 AM
Similar to p45cal's method:

Sub MFX()
Dim FX As Integer, cell As Range
'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
SpeedOn
FX = 2
For Each cell In Worksheets(1).Range("A1:A50")
If Not IsEmpty(cell) Then cell.Value2 = Round(cell.Value2 * FX, 4)
cell.NumberFormat = "0.0000"
Next cell
SpeedOff
End Sub