Consulting

Results 1 to 2 of 2

Thread: VBA: Loop a simple calculation through a 2 dimensional array

  1. #1

    VBA: Loop a simple calculation through a 2 dimensional array

    The below is what I am trying to fully recreatein VBA, except that I have 40 columns and 4,500 rows and loads more gridssimilar to this. This is all currently in an Excel model that takes 90 secondsto recalc (no volatile formulas).

    =(B$1=$P2)*1


    	                              1	                 2	               3	            4	                5
    Asset_0001	=(B$1=$P2)*1	=(C$1=$P2)*1	=(D$1=$P2)*1	=(E$1=$P2)*1	=(F$1=$P2)*1
    Asset_0002	=(B$1=$P3)*1	=(C$1=$P3)*1	=(D$1=$P3)*1	=(E$1=$P3)*1	=(F$1=$P3)*1
    Asset_0003	=(B$1=$P4)*1	=(C$1=$P4)*1	=(D$1=$P4)*1	=(E$1=$P4)*1	=(F$1=$P4)*1
    Asset_0004	=(B$1=$P5)*1	=(C$1=$P5)*1	=(D$1=$P5)*1	=(E$1=$P5)*1	=(F$1=$P5)*1
    Asset_0005	=(B$1=$P6)*1	=(C$1=$P6)*1	=(D$1=$P6)*1	=(E$1=$P6)*1	=(F$1=$P6)*1
    Asset_0006	=(B$1=$P7)*1	=(C$1=$P7)*1	=(D$1=$P7)*1	=(E$1=$P7)*1	=(F$1=$P7)*1
    Asset_0007	=(B$1=$P8)*1	=(C$1=$P8)*1	=(D$1=$P8)*1	=(E$1=$P8)*1	=(F$1=$P8)*1
    Asset_0008	=(B$1=$P9)*1	=(C$1=$P9)*1	=(D$1=$P9)*1	=(E$1=$P9)*1	=(F$1=$P9)*1
    Asset_0009	=(B$1=$P10)*1	=(C$1=$P10)*1	=(D$1=$P10)*1	=(E$1=$P10)*1	=(F$1=$P10)*1
    Asset_0010	=(B$1=$P11)*1	=(C$1=$P11)*1	=(D$1=$P11)*1	=(E$1=$P11)*1	=(F$1=$P11)*1
    I realize the below is pathetic, but it's all Ihave after hours of googling and a Udemy course on VBA arrays (yes make jokes)I would by any court or book but I must not know what I'm looking for because Ican't seem to find the wording that covers what I want to do.
    Sub AssetRecovered()
    'Dim Quarters1to40 As Variant
    'Dim expensesTo As Variant
    Dim AssetRecovered(1 To 10, 1 To 40)
     
    xArr = Range("Quarters_1to40")   
    yArr = Range("expenses_To")
     
    CalcARFormula = Asset_Recovered_Formula(AssetRecovered, xArr, yArr)
             
    End Sub
    
    Function Asset_Recovered_Formula(AssetRecovered, xArr, yArr)
    For x = LBound(AssetRecovered) To UBound(AssetRecovered)
    AssetRecovered(x) = xArrx(x) * yArr(x)
    Next x
    End Function
    OK, any and all help or pointers areappreciated, I do feel stupid asking but am at my wits end.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I think a small sample workbook and a clear description with results of a 'before and after' or 'inputs to outputs' would be helpful
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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