+ Reply to Thread
Results 1 to 9 of 9

Multiplying cells

Hybrid View

  1. 04-10-2010, 05:06 PM #1
    epione
    epione is offline
    Registered User
    Join Date
    01-26-2010
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    37

    Question Multiplying cells

    Hi,

    I am trying to multiply some cells in VBA.

    The problem is that I use a loop (until "iteration") and the number of cell where the multiplication should be done is unknown.

    I have 5 columns and I want in in column 4 starting in E4 until "iteration#":
    take the value from column 1 (same row) * H3 +
    take the value from column 2 (same row) * H4 +
    take the value from column 3 (same row) * H5 +

    In column 5 beginning in F5 until "iteration#" I want
    take the value from column 4 * M4 + M5* N5

    \1

    If it is not much more difficult I would actually prefer to have a code that takes H4-H6 and M4-M5 and N5 from a differen sheet.

    I started off with something like this, but it wouldnt work:

    Range(Cells(4, 5), Cells(Iterations + 3, 5)).Value = Range(Cells(Iterations + 3, 5), Cells(Iterations + 3, 5)).Offset(0, -3).Value * Range("H3").Value + Range(Cells(Iterations + 3, 5), Cells(Iterations + 3, 5)).Offset(0, -3).Value * Range("H4").Value + Range(Cells(Iterations + 3, 5), Cells(Iterations + 3, 5)).Offset(0, -3).Value * Range("H5").Value

    Range(Cells(4, 6), Cells(Iterations + 3, 6)).Value = Range(Cells(Iterations + 3, 6), Cells(Iterations + 3, 6)).Offset(0, -1).Value * Range("M4").Value + Range("N5").Value * Range("M5").Value
    I would appreciate any help asap!!

    Cheers
    Attached Images Attached Images
    Last edited by epione; 04-11-2010 at 09:37 PM.
    Reply With QuoteRegister To Reply
  2. 04-10-2010, 05:34 PM #2
    abousetta
    abousetta is offline
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Please help multiplying cells!!!

    Hi epione,

    Could you please upload a dummy workbook? It will be easier than having to type all the data from the pic you uploaded.

    Good luck.

    Abousetta
    Reply With QuoteRegister To Reply
  3. 04-10-2010, 05:54 PM #3
    epione
    epione is offline
    Registered User
    Join Date
    01-26-2010
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Please help multiplying cells!!!

    Sure!

    Please be aware, that I constructed this one only not to mess up my original file and it does not contain the Iteration.

    My iteration is given by the user:

    Iterations = Worksheets("Assumptions").Range("Z8").Value
    and the loops looks like this (this is the one for the third column D):

    With Worksheets("Simulation" & wc).Select
    ReDim NPV(Iterations, 1)
    For Count = 1 To Iterations
    NPV(Count, 1) = Range("Projections!D48")
    Calculate
    Next
    Range(Cells(4, 4), Cells(Iterations + 3, 4)) = NPV()
    Cheers
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 04-10-2010, 06:08 PM #4
    abousetta
    abousetta is offline
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Please help multiplying cells!!!

    Hi,

    I am trying to replicate your calculations. I am assuming that the results in the greeen and red columns are correct.

    But one problem, you refere to H6 but there is nothing is H6. Are all the three % shifted up one column?

    Abousetta
    Reply With QuoteRegister To Reply
  5. 04-10-2010, 06:19 PM #5
    epione
    epione is offline
    Registered User
    Join Date
    01-26-2010
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Please help multiplying cells!!!

    Don't worry about any values that are already in the green and red column!!!!!! Sorry, I should have put them out. They are totally random!! I think I just had them there for another test..

    You are right, it is supposed to be H3:H5, I changed my first post.
    Reply With QuoteRegister To Reply
  6. 04-10-2010, 07:30 PM #6
    epione
    epione is offline
    Registered User
    Join Date
    01-26-2010
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Please help multiplying cells!!!

    .. anyone???

    if it's unclear I am very willing to try to explain it again more pragmatic??!!
    Last edited by epione; 04-10-2010 at 08:07 PM.
    Reply With QuoteRegister To Reply
  7. 04-10-2010, 08:41 PM #7
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Multiplying cells

    if it's unclear ...
    It is to me.

    Suggest you explain what you're trying to do, rather than how you're trying to get there, and post a workbook with the minimum amount of data necessary to demonstrate.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  8. 04-10-2010, 09:51 PM #8
    epione
    epione is offline
    Registered User
    Join Date
    01-26-2010
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    37

    Question Re: Multiplying cells

    Hi shg,

    thanks for trying to help!

    I have redone the sheet to better examply what I try to do.

    In my real file, Sheet3 is a new created sheet, so the name will always be different (Result1, Result2, Results3, but I think it does not matter for this problem.)

    Sheet1 and 2 have Information. Sheet3 calculates values based on this information.

    Columns A:D in Sheet3 are working fine. Now I am struggling with Column E and F. I have put in the formulas as I need them to be calculated by VBA. They should be calculated for as many rows as given as iterations in Sheet1 (similar as A:D are doing it).

    I would highly appreciate if someone could help me with his.. it's the last step in my project

    Cheers
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 04-10-2010, 11:52 PM #9
    epione
    epione is offline
    Registered User
    Join Date
    01-26-2010
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Multiplying cells

    Alright, i found someone who helped me

    Solution:

    Set wksA = Worksheets("Simulation" & wc)
    wksA.Range(wksA.Cells(4, 5), wksA.Cells(Iterations + 3, 5)).FormulaR1C1 = "=(RC2*'Assumptions'!R14C10)+(RC3*'Assumptions'!R15C10)+(RC4*'Assumptions'!R16C10)"

    wksA.Range(wksA.Cells(4, 6), wksA.Cells(Iterations + 3, 6)).FormulaR1C1 = "=(RC5*'Assumptions'!R22C6)-('Assumptions'!R43C6*(1-'Assumptions'!R22C6))"
    Finishing time!

    Cheers
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

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


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1