Results 1 to 8 of 8
  1. #1
    Gold Lounger
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: value for year.. (2000)

    I have no idea what you mean. Please explain.

  3. #3
    Gold Lounger
    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...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: value for year.. (2000)

    Why don't you want to use formulas?

  5. #5
    Gold Lounger
    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  7. #7
    Gold Lounger
    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •