-
Multiply selection by a variable
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....
-
At its simplest from a coding point of view:
[vba]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[/vba] 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):[vba]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[/vba]
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
Similar to p45cal's method:
[VBA]
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[/VBA]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules