Results 1 to 8 of 8
Thread: value for year.. (2000)

20080401, 09:52 #1
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
value for year.. (2000)
how to calculate the value in year for each value in column A... and return the result in column C
In B the number of month.
Via code if is possible, no formula.
Naturally, not use in code, for the value that have in column B 12 month.

20080401, 10:04 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: value for year.. (2000)
I have no idea what you mean. Please explain.

20080401, 10:30 #3
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: value for year.. (2000)
calculate the value of year based 12 month...
In A2 is the total ammount of year, this value is ok because have 12 month.
In A4 i have only the value of amont for one month (see column B in B4) in this case i want to calcultae the other 11 month to have the total of 1 year .
eaxample:
i have 1 month = 4.550
total of year is 4.550*11 + 4.550
...
hope now i am clear...

20080401, 11:13 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: value for year.. (2000)
Why don't you want to use formulas?

20080401, 11:38 #5
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: value for year.. (2000)
OK... for formulas,
Tks.

20080401, 11:49 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: value for year.. (2000)
Enter the following formula in C2:
=12*A2/B2
and fill down.
Or using code:
<code>
Sub ComputeYearlyAmount()
Dim r As Long
Dim m As Long
m = Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To m
Range("C" & r) = 12 * Range("A" & r) / Range("B" & r)
Next
Range("C1") = "Annuale"
Range("A1:A" & m).Copy
Range("C1:C" & m).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub</code>

20080401, 12:48 #7
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: value for year.. (2000)
WOW!!!
Work perfect!
Only one...
How to around the result of Range("C" & R) To index 10... (in italian ARROTONDA PER ECCESO)
Example:
Range("C" & R)= 9.815.186
and i want to have 9.815.190
or
Range("C" & R)= 9.815.164
and i want to have 9.815.160
in effect if lat value >5 or <5 arround to 10

20080401, 13:18 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: value for year.. (2000)
You can use this line:
<code>
Range("C" & r) = Application.Round(12 * Range("A" & r) / Range("B" & r), 1)
</code>
The worksheet function Round lets you round to a multiple of 10 by specifying 1 as the number of decimal places.